Save and Retrieve VBA Variables in the Windows Registry

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

The following VBA code may be used as an example of how to read, write, and delete these registry settings.
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 DevelopmentProduct Details

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.

Product DetailsExcel 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.




Don't leave your friends behind... share this!

Leave a Reply

Your email address will not be published. Required fields are marked *