Using The GetSetting & SaveSetting Functions

Category: VBA Functions | [Item URL]

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).