There will come a time in Visual Basic for Application (VBA) programming when you will want to save a variable that you are able to modify whose value remains persists between sessions. Such a variable is particularly useful when saving users specific settings in your application, maintaining software license keys, or storing anything else you may wish.
One way of doing this is to save the variable within a hidden (or very hidden) worksheet and hope the user doesn’t corrupt the data. Another way of doing this is to save the variable to a file and retrieve it later as needed, but this is a fairly involved process. The cleanest and simplest method I’ve found to preserve these variables is to write them to the Windows registry using readily available VBA functions.
There are 3 VBA functions that are of interest to this approach:
- SaveSetting
- GetSetting
- DeleteSetting
SaveSetting() Function
Public Function SaveSetting(AppName As String, Section As String, Key As String, Setting As String)
GetSetting() Function
Public Function GetSetting(AppName As String, Section As String, Key As String, Optional Default As String) As String
DeleteSetting() Function
Public Function DeleteSetting(AppName As String, Optional Section As String, Optional Key As String) As String
Function Parameters
AppName - String expression containing the name of the application or project to which the setting applies.
Section - String expression containing the name of the section in which the key setting is being saved.
Key - String expression containing the name of the key setting being saved.
Setting - String expression containing the value to which Key is being set.
Default - String expression containing the value to return if no value is set in the Key setting. If omitted, assumed to be a zero-length string ("").
Registry Location Manipulated
HKEY_CURRENT_USER\Software\VB and VBA Program Settings\AppName\Section\Key\Setting
Example
Sub Test_Registry_Functions()
‘Save Setting to Registry
SaveSetting "MyApp", "Startup", "Top", "75"
‘Retrieve and Display Setting in Message Box
MsgBox GetSetting ("MyApp", "Startup", "Top")
‘Delete Registry settings
DeleteSetting ("MyApp")
End Sub
References
Subscribe to the blog in the right side panel.
Book Recommendations
Professional Excel Development
The best resource I’ve found to take you’re VBA development skills to the next level, including:
- Application development best practices
- Protect your source code within a .dll file
- Create an installer for your application
- Code execution optimization
- …and much more.
Excel 2013 Power Programming with VBA
An excellent introduction and ongoing reference for VBA development, including:
- Developing VBA functions and procedure
- Data types (string, integer, variant, etc.)
- Common programming structures (If-Then-Else logic, For loops, Do loops, etc.)
- …and much more.
