There are a number of built-in functions / statements that allow VBA code to work with the Registry:
These built-in functions / statements are designed to allow VBA programs to save simple values to the Registry such that they can be read-back later, perhaps even days or years later, when the values stored in any variables in your VBA code would have been long-ago lost.
The built-in functions / statements are also designed to take the complexity out of working with the Registry ... you don't really have to know anything about hives, keys, value names/types/data etc ... but if you do want to know more:
You may want to start with the Microsoft article Windows registry information for advanced users (not that advanced, I think!)
And understand that all of the built-in functions / statements operate within the "HKEY_CURRENT_USER\Software\VB and VBA Program Settings" key within the Registry.
I have a few issues with each of these built-in functions / statements, though, and so I have created some wrapper code around them (which you can view and download, below) that I use when working with them:
The first parameter of each of them is 'AppName' (a String) ... this should always be "the name of the application or project to which the setting applies" (Microsoft's words, not mine) ... as such, it should always be the same value for any specific application/project you are working on .... and so I use my gsAPP_NAME constant that I declare in every VBA project ... and as I always use the same constant, I don't want to have to type it out every time I want to create or read a value in the Registry, instead I put the constant directly into the statement / function. To use the code below, you will need to declare a gsAPP_NAME constant in your VBA Project.
The names of the parameters used in each of the built-in functions / statements annoys me ... each of them includes a 'Section' parameter and (other than GetAllSettings) a 'Key' parameter. The thing that annoys me is that 'Section' actually determines the key that is written to and 'Key' determines the value name that is written to. Small stuff. And pretty much invisible if all you ever do is use the functions / statements to read and write simple values. But it makes things confusing if you then use such as RegEdit (see below) to view the Registry.
Given the above and the optional nature of the 'Section' and 'Key' parameters of the DeleteSetting built-in statement, I find it extremely confusing to know if I am actually deleting a value or a key.
If you save a Boolean with SaveSetting then it writes the value to the Registry as a String using the "Office Display Language" e.g. if the Boolean holds a True value then if your "Office Display Language" is English then it will write "True" but if your "Office Display Language" is Spanish then it will write "Verdadero". When you subsequently read this value, GetSetting will provide you with the exact same String value and you can use CBool() to convert that back to a True or False value. But if the "Office Display Language" has changed in the meantime then CBool() won't give you a True or False value, instead it will give you an error 13 "Type mismatch" as "Verdadero" doesn't mean anything in the English language. The code below avoids this problem by converting the Boolean to a Long (0 for False and -1 for True) before it is written to the Registry which CBool() can handle in any language. This issue might not be significant for you if you just write VBA code for your own use and you only speak one language ... but if you are writing code for multi-lingual users then this certainly can become an issue!
There is no explicit way to test if a specific value already exists in the Registry.
There is no explicit way to test if a user actually has read / write access to the Registry. Even for the "HKEY_CURRENT_USER" hive, which the "VB and VBA Program Settings" key is within, a user can be blocked (e.g. via group policy) from reading and / or writing values ... not likely, but it is possible.
Viewing the Registry keys and values using RegEdit
At times, it's useful to be able to view the contents of the Registry directly, without having to use code. To do this, use the RegEdit tool (see following image). Hit the Windows button then type "regedit" (without the speech marks).
To see the values created using the built-in functions / statements, you will then have to navigate to the "HKEY_CURRENT_USER\Software\VB and VBA Program Settings" key ... first expand "HKEY_CURRENT_USER", then within that expand "Software", then within that expand "VB and VBA Program Settings". It's possible that the "VB and VBA Program Settings" key may not exist on your device. If not, don't worry ... it will be added automatically as soon as you use SaveSetting from your VBA code.
Above is an example (you can click it to expand it):
In the left-hand pane you can see some Registry keys such as 'Software', with that 'VB and VBA Program Settings', within that 'MyAppName' and within that 'Section' (keys are like folders in that they can contain other keys and they can also contain values which are like files).
In the right-hand pane you can see the values associated with the (highlighted / selected) "Section" key - it has 3 values: "(Default)", "Key" and "Some other Key" ... each value has a Name, Type and Data, where:
The Name is the value you supply as the "Key" parameter (confusing ... and relates my "annoyance" with the names of the parameters as noted above).
The Type is always "REG_SZ" ... every value in the Registry has a Type ... but when using the built-in functions / statements, that type is always "REG_SZ" which means a fixed-length string.
The Data is the value you supply as the "Setting" parameter when using SaveSetting.
Note that the "(Default)" value is added automatically and (so far as I know!) cannot be updated or read using the built-in functions / statements.
My code
So here is my code. I always put this into a standard Module named "UtilsRegistryLocal" (the "Local" part is because the code is updating the 'app local' "VB and VBA Program Settings" key and also I use "UtilsRegistry" as the name of a standard Module that works with the wider Registry outside of the "VB and VBA Program Settings" key ... see my Working with the Registry using VBA (the 'wider' Registry: the HKCU, HKCR and HKLM hives) post if you are interested).
This code eliminates each of my "annoyances" with the built-in functions / statements. Additionally, every procedure is documented so that you can understand its purpose, the parameters it requires and what it returns (if anything).
And remember that you will need to declare your own gsAPP_NAME constant in your VBA Project e.g.
... but changing "MyAppName" to the name of YOUR app!
The documentation should mean that knowing how to use this code should be fairly self-evident ... but note:
The vValueData parameter of SaveValue() has a Variant type. You can pass any built-in 'primitive' type in (String, Long, Boolean etc). As noted above, if it is a Boolean it will be converted to a Long to avoid localisation problems. However, the value returned by GetValue() is a String. This is because (as also noted above), all values saved to the Registry are saved as Strings. As such, to ensure you get the appropriate type of value back, you should use the relevant conversion function (unless, of course, you were expecting a String) eg CLng() for Long values, CBool() for Boolean values etc.
How to use the TryGetAllValuesForKey() and TryGetAllValueNamesForKey() procedures is a bit more complex, hence the usage examples embedded in the code comments for each of them ... you can copy these usage examples to your code, remove the 'code comment' apostrophes and customise them to your needs.
Download the code here:
Comments