Ever had an issue of sharing data (VBA settings) between your workbooks? Not many Excel VBA users know that VBA allows you to easily save and certain variables to and from the Windows Registry, just as if setting permanent VBA settings within your VBA Project. What is more this is already built into VBA that is why you do not have to reference any external libraries or functions. When is this useful:
Saving VBA settings of Excel Add-Ins – when using some VBA variables that you would like to save permanently for the user, and you would like to be able to get them at any time. Usually this applies only to saving configurations.
Sharing VBA data between workbooks – say you have multiple Excel workbooks which share some simple data fields e.g. certain variables, settings etc. Saving them into the registry is sometimes more convenient especially if the location of all workbooks can change over time.
Sharing VBA data between Excel and other applications (MS Office but not only) – technically it should be possible to get these settings from any other MS Office application e.g. MS Access or MS Word.
Source code – Saving VBA Settings to registry
Global myVariable as Integer
Sub GetMyVariable
myVariable = GetSetting("MyApplication", "MySettings", "myVariable", "0")
End Sub
Sub SaveMyVariable(newValue as Integer)
call SaveSetting("MyApplication", "MySettings", "myVariable", newValue)
End Sub
