Using The GetSetting & SaveSetting Functions
The Windows registry is a central storehouse that is used by applications to store information such as user preferences. Prior to Excel 97, accessing the registry required API calls. Excel 97 (and later versions) includes two handy VBA functions:
- GetSetting: Retrieves a setting from the registry
- SaveSetting: Saves a setting to the registry
These two functions are described in the online help, so I won't cover the details here. However, it's important to understand that these functions work only with the following key name:
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
In other words, you can't use these functions to access any key in the registry. Rather, these functions are most useful for storing information about your Excel application that you need to maintain between sessions.
The subroutine below, which is stored in the code module for the ThisWorkbook object, demonstrates the GetSetting and SaveSetting functions. This subroutine is executed when the workbook is opened. It retrieves two bits of information: the number of times the workbook has been opened; and the date and time the file was last opened. This information is displayed in a message box.
Private Sub Workbook_Open() Dim Counter As Long, LastOpen As String, Msg As String ' Get setting from registry Counter = GetSetting("XYZ Corp", "Budget", "Count", 0) LastOpen = GetSetting("XYZ Corp", "Budget", "Opened", "") ' Display the information Msg = "This file has been opened " & Counter & " times." Msg = Msg & vbCrLf & "Last opened: " & LastOpen MsgBox Msg, vbInformation, ThisWorkbook.Name ' Update the information and store it Counter = Counter + 1 LastOpen = Date & " " & Time SaveSetting "XYZ Corp", "Budget", "Count", Counter SaveSetting "XYZ Corp", "Budget", "Opened", LastOpen End Sub
The image below shows how these settings appear in the registry (using the Windows regedit.exe program).
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks