How can we store information:
- Settings
- Preferences
- Object States and positions
- …
for a VBA solution, whether that be an Access database, Excel workbook, Word Document …?
What about storing the information in the registry!
That’s the subject I would like to broach in this article.
Yes, as developers, we have several options available to use to store such information, things like:
- in tables (in Access)
- in hidden worksheets
- in external files
- …
each presents PROs and CONs, no doubt, but the reason I wanted to cover using the registry is because it is:
- built-in to VBA, so available throughout the Office suite and thus universal
- easy to use
- fast
- reliable
- Isn’t tied to a specific application, can be shared amongst multiple application if needed
The Code
As mentioned above, VBA natively has built-in the necessary code to work with an isolate section of Registry in a safe manner. So we don’t even need to get into advanced APIs (and deal with bitness issues) or ActiveX approaches… Instead, we need only call upon 4 functions/statements:
- DeleteSetting
- GetAllSettings
- GetSetting
- SaveSetting
I thinks their names are self-explanatory!
To facilitate usage slightly, I created and use a series of wrapper functions and thus below is a sample of the code I use:
Private Const sAPPNAME = "YourApplicationName"
Function SaveRegistrySetting(sSectionName As String, sKeyName As String, sSettingValue As String) As Boolean
On Error GoTo Error_Handler
Call SaveSetting(sAPPNAME, sSectionName, sKeyName, sSettingValue)
SaveRegistrySetting = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: SaveRegistrySetting" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Function GetRegistrySetting(sSectionName As String, sKeyName As String) As String
'Returns "" if app, section or key are not found
'Always returns a VarType = vbString / TypeName = String (stored as REG_SZ), so use conversion function after retrieving as req'd
On Error GoTo Error_Handler
GetRegistrySetting = GetSetting(sAPPNAME, sSectionName, sKeyName)
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: GetRegistrySetting" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Function GetAllRegistrySettings(sSectionName As String) As Variant
On Error GoTo Error_Handler
Dim aSectionSettings As Variant
Dim iCounter As Long
aSectionSettings = GetAllSettings(sAPPNAME, sSectionName)
GetAllRegistrySettings = aSectionSettings
If IsEmpty(aSectionSettings) = True Then
GetAllRegistrySettings = Null
Else
For iCounter = LBound(GetAllRegistrySettings, 1) To UBound(GetAllRegistrySettings, 1)
Debug.Print aSectionSettings(iCounter, 0), aSectionSettings(iCounter, 1)
Next iCounter
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: GetAllRegistrySettings" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Function DeleteRegistrySetting(sSectionName As String, Optional sKeyName As String) As Boolean
On Error GoTo Error_Handler
If sKeyName = "" Then
'Delete the entire section and all its keys
Call DeleteSetting(sAPPNAME, sSectionName)
Else
'Delete a specific section/key
Call DeleteSetting(sAPPNAME, sSectionName, sKeyName)
End If
DeleteRegistrySetting = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
If Err.Number <> 5 Then
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: DeleteRegistrySetting" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function
Using such an approach, simplifies usage, simplifies updating the ‘YourApplicationName’ and adds error handling throughout.
Usage Examples
Save A ‘Setting’
To save a value to the Registry, we can simply do:
? SaveRegistrySetting("Application Settings", "DefaultSortOrder", "DESC")
Or
? SaveRegistrySetting("Application Settings", "EnablePreview", True)
OR
? SaveRegistrySetting("Application Settings", "NumberDecimals", 3)
which will return True/False to indicate whether the Save was successful or not.
Retrieving A ‘Setting’
To read back, retrieve, a setting value from the Registry, we can simply do:
? GetRegistrySetting("Application Settings", "DefaultSortOrder")
which will return
DESC
Retrieving All The ‘Settings’
If we want to review the current setting in one shot, perhaps build a collection/array/… the can use the GetAllRegistrySettings().
GetAllRegistrySettings("Application Settings")
which then returns
DefaultSortOrder DESC EnablePreview True NumberDecimals 3
Deleting A Single ‘Setting’
To delete a setting from the Registry entirely, we can simply do:
? DeleteRegistrySetting("Application Settings", "DefaultSortOrder")
which will return True/False to indicate whether the Deletion was successful or not.
Deleting An Entire Section
To delete all the Settings within a Section and delete the section itself, we can simply do:
? DeleteRegistrySetting("Application Settings")
which will return True/False to indicate whether the Deletion was successful or not.
Things To Be Aware Of
The Importance Of YourApplicationName!
The important thing here is to use an ‘YourApplicationName’ that is unique, so it can never be accidentally used by someone else in their program. Using a name like Access, ContactDb, … are probably poor choices. Instead, try prefixing with a corporate name, or even random strings that will never be duplicated: CARDA_ContactDb, gjh324_ContactDb
Strings Everywhere
One thing to note is that everything gets stored and thus retrieved as Strings. So you, as the developer, will need to be aware of the data you are retrieving and use the appropriate conversion function
- CBool
- CDate
- CInt
- CLng
- …
to convert the String into the appropriate variable type for further use within your VBA code.
Where Is It Stored
As you can see by my functions, in their usage, we never supply a root Registry Key where we store this information. So where exactly is it stored? That’s the beauty here, behind the scenes, all these procs work and Read/Update/Delete your values to the Root Key of:
Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\YourApplicationName
and store all the information there under different sections, as you define them when you call the various procs.
So you can launch the Registry Editor anytime you want, navigate to that section of the registry, and you can then review exactly how these interact and make changes.
Useful Resources
