How to Store Program Settings in the Registry from Excel Visual Basic

Windows stores program settings in the Registry, a Windows internal database.  You can use this to remember previous user choices and use them as defaults the next time.  We typically remember window positions, file paths, and other settings with the Registry.

These three commands work with items stored in the Registry:

SaveSetting appname, section, key, setting
rtn = GetSetting(appname, section, key[, default])
DeleteSetting appname, section[, key]

Item	Description
appname	String containing the name of the application or project.
section	String containing the name of the section where the key setting saves.
key	String containing the name of the key setting.
setting	String containing the key value.
default	Optional value to return if the key setting has no value set. If omitted, and there is no key value. the GetSetting command returns a zero-length string (“”).

1.        Example

This is the Module named Registry in our sample file Extras.xlsm:

Option Explicit
Dim FilePath$

Sub Demo()      ' Show use of Registry to save settings:
  SaveSettings

  GetSettings
 
  MsgBox FilePath$, vbInformation, "The Saved Path is:"
End Sub

Sub SaveSettings()
  FilePath$ = ActiveWorkbook.FullName   ' Full Path and File Name
  SaveSetting "MyApp", "Settings", "LastFile", FilePath$
End Sub

Sub GetSettings()
  FilePath$ = GetSetting("MyApp", "Settings", "LastFile")
End Sub

2.        Where Can I See the Registry Settings?

Technical Note:  You can view the settings with the Registry Editor (RegEdit).  You can run RegEdit from Windows Search.  The settings will be under either the key
HKEY_CURRENT_USER\Software\VB and VBA Program Settings or
HKEY_USERS\Default\Software\VB and VBA Program Settings:

Here we see the settings stored by a stand-alone VB program, DirSize.  It stores:

• The size of the program window (height and width),
• The location of the program window (left and top),
• The Window State (minimized, maximized, or restored), and
• The folder path that the program analysed.