Reading from and writing to the Registry

Most Windows applications use the Windows Registry database to store settings. (See Chapter 4 for some additional information about the Registry.) Your VBA procedures can read values from the Registry and write new values to the Registry. Doing so requires the following Windows API declarations:

Private Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long) As Long

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" _ (ByVal hKey As Long) As Long

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sValueName As String, _ ByVal dwReserved As Long, ByVal dwType As Long, _ ByVal sValue As String, ByVal dwSize As Long) As Long

Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sSubKey As String, _ ByRef hkeyResult As Long) As Long

Private Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" _ (ByVal hKey As Long, ByVal sValueName As String, _ ByVal dwReserved As Long, ByRef lValueType As Long, _ ByVal sValue As String, ByRef lResultLen As Long) As Long

I developed two wrapper functions that simplify the task of working with the Registry: GetRegistry and WriteRegistry.These functions are available on the companion CD-ROM.This workbook includes a procedure that demonstrates reading from the Registry and writing to the Registry.

READING FROM THE REGISTRY

The GetRegistry function returns a setting from the specified location in the Registry. It takes three arguments:

♦ RootKey: A string that represents the branch of the Registry to address. This string can be one of the following:

HKEY_CLASSES_ROOT

HKEY_CURRENT_USER

HKEY_LOCAL_MACHINE

HKEY_USERS

HKEY_CURRENT_CONFIG

HKEY_DYN_DATA

♦ Path: The full path of the Registry category being addressed.

♦ RegEntry: The name of the setting to retrieve.

Here's an example. If you'd like to find out the current setting for the active window title bar, you can call GetRegistry as follows. (Notice that the arguments are not case-sensitive.)

RootKey = "hkey_current_user" Path = "Control Panel\Colors" RegEntry = "ActiveTitle"

MsgBox GetRegistry(RootKey, Path, RegEntry), _ vbInformation, Path & "\RegEntry"

The message box will display three values, representing the red/green/blue (RGB) value of the color.

WRITING TO THE REGISTRY

The WriteRegistry function writes a value to the Registry at a specified location. If the operation is successful, the function returns True; otherwise, it returns False. WriteRegistry takes the following arguments (all of which are strings):

♦ RootKey: A string that represents the branch of the Registry to address. This string may be one of the following:

HKEY_CLASSES_ROOT

HKEY_CURRENT_USER

HKEY_LOCAL_MACHINE

HKEY_USERS

HKEY_CURRENT_CONFIG

HKEY_DYN_DATA

♦ Path: The full path in the Registry. If the path doesn't exist, it is created.

♦ RegEntry: The name of the Registry category to which the value will be written. If it doesn't exist, it is added.

♦ RegVal: The value that you are writing.

Here's an example that writes a value representing the time and date Excel was started to the Registry. The information is written in the area that stores Excel's settings.

Sub Auto_Open()

RootKey = "hkey_current_user"

Path = "software\microsoft\office\11.0\excel\LastStarted" RegEntry = "DateTime" RegVal = Now()

An Easier Way to Access the Registry

If you want to use the Windows Registry to store and retrieve settings for your Excel applications, you don't have to bother with the Windows API calls. Rather, you can use VBA's GetSetting and SaveSetting functions.

These two functions are described in the online help, so I won't cover the details here. However, it's important to understand that these functions work only with the following key name:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings

In other words, you can't use these functions to access any key in the Registry. Rather, these functions are most useful for storing information about your Excel application that you need to maintain between sessions.

If WriteRegistry(RootKey, Path, RegEntry, RegVal) Then msg = RegVal & " has been stored in the registry." Else msg = "An error occurred"

End If MsgBox msg End Sub

If you store this routine in your personal macro workbook, the setting is automatically updated whenever you start Excel.

0 0

Post a comment