If you want to save data from one Excel session to another, you can store this in the Windows registry. Using the registry is good for saving things like user preferences or program configuration data.
There are other means of saving this type of data like external files, hidden sheets and defined names, but I'm just looking at using the registry in this post.
This code works on a Macintosh too, with the information being saved in the application's initialization file.
What is the Registry?
The Windows registry is a database that stores configuration settings for Windows and the programs that run on Windows.
Data in the registry can be viewed and modified using the Windows programs regedt32.exe and regedit.exe, so bear that in mind if you are storing important information there.
Be very careful if you are not familiar with using either of these programs. If you modify registry settings incorrectly it can cause unpredictable results on your Windows computer.
Remember that these settings will only be recorded in the registry of your computer. If you open the workbook on another computer, you are accessing that computer’s registry so the data there will be different.
A trivial example
For the purpose of this example let's do something simple like count the number of times the workbook is opened.
To do this I will save a value to the registry, read it when the workbook is opened and display a message on screen showing how many times the workbook has been opened.
This value will then be incremented and saved back to the registry.
I'll also ask if you want to reset the count, and if you do I will delete the entry in the registry.
You can of course get a bit more exciting with the data you save to the registry. You could use a userform to ask the user for information, or save data directly off a sheet, it’s up to you.
Saving settings for a single workbook
If you want to save some settings for a single workbook then you can store the VBA in the workbook itself, in the ThisWorkbook module.
Saving settings for all workbooks
If you want to save settings for all workbooks, then you need to create a PERSONAL.XLSB and store the VBA in its ThisWorkbook module.
Events
In order to get the VBA to run, I'm using events.
As I want to run code in a single workbook when it is opened I’ll use the Workbook_Open() event.
For code we want to run for every workbook, it can be a little more complicated.
To get PERSONAL.XLSB to run our code every time a workbook opens, we need to get the application (Excel) events handled by PERSONAL.XLSB. We can then use the App_WorkbookOpen() event to run code every time a workbook is opened.
To set all this up we use the following code
Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub
What this is saying is:
Declare a variable called App as an application type object and handle events with it.
Private WithEvents App As Application
When a workbook is opened, set the App variable to be our application (Excel).
Private Sub Workbook_Open()
Set App = Application
End Sub
Events in the application (Excel) are now handled by PERSONAL.XLSB and we can capture workbook opens by using App_WorkbookOpen().
The Functions That Do The Work
There are only three functions we need to write, read and delete data from the registry.
SaveSetting
This saves your data to the registry into HKEY_CURRENT_USER\Software\VB and VBA Program Settings. If this doesn’t exist already it’ll be created the first time SaveSetting is used.
The official Microsoft declaration for this is
SaveSetting appname, section, key, setting
appname is any name you care to use. Confusingly, it does not mean Excel. I am using MOTH_Excel_Settings
An error occurs if the setting can't be saved for any reason so appropriate steps should be taken to allow for this. Sounds like I should write a blog post on error handling.
If the registry, or the parts you are accessing, are protected then the code may not be able to modify the registry.
The section can refer to what you are storing the data for, so in my case I am using WorkBook_Opens
They key is the name of the data. If I am storing a value for each workbook I open, I can use the name of the workbook itself.
The setting is the value of the data, which in my case is the number of times the workbook has been opened.
Once my macro has run the data in the registry looks like this:
GetSetting
GetSetting appname , section, key [, default ]
GetSetting returns the data specified by appname , section, key
default is an optional parameter. If you supply it when calling GetSetting, then that default value is returned if GetSetting can't find the data at appname , section, key
This is useful for checking the existence of some data.
DeleteSetting
DeleteSetting appname [, section] [, key]
Deletes an entry from the registry, or on a Mac, deletes the data in the application's initialization file.
Both the section and key are optional parameters. But you can't specify a key without a section
An error occurs if you try to delete something that isn't in the registry.
By specifying a key, you can delete just that key. If you don’t specify the key, then the whole section is deleted. If you specify neither section nor key then everything under appname is deleted.
Sample Workbooks & Code
Enter your email address below to download the sample workbooks.
VBA for a Single Workbook
Remember that if you just want to record data for a single workbook, you can store the VBA in that workbook.
VBA for All Workbooks
This is the code you use to record data for all workbooks you open. This is just a sample workbook with the code you need. You must create your own PERSONAL.XLSB and put the code in there for it to work on all workbooks.
The sub SaveRegSettings that does the hard work is almost exactly the same as the code for recording the number of opens for a single workbook. The difference being that when deleting the registry data, the code deletes the key for the workbook, not all the data under AppName.
DeleteSetting AppName, Section, ActiveWorkbook.Name
Excellent blog… Really easy to understand
Thanks Ron