Saving Application Settings in the Registry
In a VBA procedure, you use variables to store values you need to use while you're running the procedure. When the procedure finishes, the values of those variables are wiped from memory. What do you do if you have values that you want to preserve from one VBA session to another? You could store the values somewhere in the document, but this isn't a great idea because those values could be easily changed or even deleted.
A better idea is to use the Registry. Windows uses the Registry to store thousands of settings related to software, hardware, and user options. Not only that, but most applications make use of the Registry as a place to store setup options, customization values selected by the user, and much more. VBA doesn't enable you to access the Registry as a whole. Instead, it provides you with a special key in which you can add, read, change, and delete your own keys, settings, and values. VBA also provides a number of statements that enable you to perform these Registry tasks, and the next few sections show you how to use these statements.
Storing Settings in the Registry
To store a setting in the Registry, use the SaveSetting statement:
SaveSetting appname, section, key, setting
appname
The name you want to use to identify your application in the Registry.
section
The section in which to store the value. This will be a subkey of the appname key.
key
The name of the key setting that you want to store.
setting
The value to which key is being set.
When you run this statement, VBA creates a new key in the Registry, as follows:
\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\appname\section\
The key setting is added to this subkey, and its value is set to setting.
Reading Settings from the Registry
After you've stored a value in the Registry, you can read that value by using the GetSetting statement:
GetSetting(appname, section, key[, default])
appname
The name you're using to identify your application in the Registry.
section
The section in which the value is stored.
key
The name of the key setting that you want to retrieve.
default
(Optional) The value to be returned if key doesn't exist or isn't set.