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:
Public Function SaveSetting(AppName As String, Section As String, Key As String, Setting As String)
Public Function GetSetting(AppName As String, Section As String, Key As String, Optional Default As String) As String
Public Function DeleteSetting(AppName As String, Optional Section As String, Optional Key As String) As String
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
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
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.
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.