Using the GetSetting and 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.
An example
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).
