03 September 2007 @ 03:34 pm
Read and Write Windows Registry with VBA  
VBA offers the functions GetSetting, SaveSetting, GetAllSettings and DeleteSetting for reading and writing the Windows Registry. (For detailed info see the Microsoft VBA help for these functions or look at the MSDN)

But unfortunately you can't access the entire registry, only the path HKEY_CURRENT_USER\Software\VB and VBA Program Settings\. So you can create, save and read user settings for your own Visual Basic application, but you can't access settings for any other application (e.g. the Default download directory for the Internet Explorer, which, btw, is HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Download Directory)

Fortunately, when programming with VBA, you're not limited to what functionality VBA offers. By making use of Windows Scripting features, you can access the entire Window Registry quite easily.

Reading from the Registry:

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

Checking if a Registry key exists:

'returns True if the registry key i_RegKey was found
'and False if not
Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
  
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Saving a Registry key:

'sets the registry key i_RegKey to the
'value i_Value with type i_Type
'if i_Type is omitted, the value will be saved as string
'if i_RegKey wasn't found, a new registry key will be created
Sub RegKeySave(i_RegKey As String, _
               i_Value As String, _
      Optional i_Type As String = "REG_SZ")
Dim myWS As Object

  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'write registry key
  myWS.RegWrite i_RegKey, i_Value, i_Type

End Sub

Deleting a key from the Registry:

'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function

These functions need the Registry key with its complete path, so i_RegKey must always begin with one of the following values:
  • HKCU or HKEY_CURRENT_USER
  • HKLM or HKEY_LOCAL_MACHINE
  • HKCR or HKEY_CLASSES_ROOT
  • HKEY_USERS
  • HKEY_CURRENT_CONFIG
and end with the name of the key...

RegKeySave also has an input parameter for the type of the Registry key value. Supported are the following types:
  • REG_SZ - A string. If the type is not specified, this will be used as Default.
  • REG_DWORD - A 32-bit number.
  • REG_EXPAND_SZ - A string that contains unexpanded references to environment variables.
  • REG_BINARY - Binary data in any form. You really shouldn't touch such entries.
You can find more info about Registry value types in the MSDN.


Finally, here's a little program for testing the above functions:

Sub TestRegistry()
Dim myRegKey As String
Dim myValue As String
Dim myAnswer As Integer

  'get registry key to work with
  myRegKey = InputBox("Which registry key do you want to read?", _
             "Get Registry Key")
  If myRegKey = "" Then Exit Sub
  'check if key exists
  If RegKeyExists(myRegKey) = True Then
    'key exists, read it
    myValue = RegKeyRead(myRegKey)
    'display result and ask if it should be changed
    myAnswer = MsgBox("The registry value for the key """ & _
               myRegKey & """" & vbCr & "is """ & myValue & _
               """" & vbCr & vbCr & _
               "Do you want to change it?", vbYesNo)
  Else
    'key doesn't exist, ask if it should be created
    myAnswer = MsgBox("The registry key """ & myRegKey & _
               """ could not be found." & vbCr & vbCr & _
               "Do you want to create it?", vbYesNo)
  End If
  If myAnswer = vbYes Then
    'ask for new registry key value
    myValue = InputBox("Please enter new value:", _
              myRegKey, myValue)
    If myValue <> "" Then
      'save/create registry key with new value
      RegKeySave myRegKey, myValue
      MsgBox "Registry key saved."
    End If
  End If
  
  'ask if key should be deleted from registry
  myAnswer = MsgBox("Do you want to delete the registry key """ & _
             myRegKey & """?", vbYesNo)
  If myAnswer = vbYes Then
    'delete registry key
    If RegKeyDelete(myRegKey) = True Then
      'deletion was successful
      MsgBox "Registry key """ & myRegKey & """ deleted."
    Else
      'deletion wasn't successful
      MsgBox "Registry key """ & myRegKey & _
             """ could not be deleted."
    End If
  End If
End Sub

Of course, the usual warnings apply:
Don't mess around with the Registry if you don't know what you're doing, or it could have severe consequences! If you change entries you're not too sure about, backup the registry first! Don't delete entries, better rename them, so you can easily get them back if your change didn't what you wanted! Only delete them when you're absolutely sure you (and neither Windows nor any application) don't need them anymore! You change the Registry at your own risk!

 
 
 
( 3 comments — Leave a comment )
(Anonymous) on June 26th, 2009 05:00 pm (UTC)
Just what was needed
This is exactly what I needed to be able to create Access Database code that would check the registry to help link backend tables through VBA code.
I have a program that goes to 12 different locations and each location uses it's own backend. Of course, the backend address/URL in each location is different.
I had started to use a database table to make updating of the links easier, but it has drawbacks if a WAN connection is involved in the first link.
Using a registry check was a better answer but I wanted the information in HKLM and not HKCU. Your code provided the means to make it happen.
I still have some fine tuning to do, but the solution is more elegant and will make program updates easier.
(Anonymous) on July 25th, 2009 08:53 pm (UTC)
AWESOME DUDE!
i really appreciate your work. i rewrote the code to stop outlook express from auto running the compacting file dialog box-which opens after opening OE 100 times - and was screwing up my automation. I just found the "Compact Check Count" key, removed all code that could screw up my registry (by accident) and voila. Beauty that couldn't have been done withoutcha!

Sub compactCheckCountRegistryChange()
Dim myRegKey As String
Dim myValue As String
Dim myAnswer As Integer

myRegKey = "HKEY_USERS\ (find your own numbers) \Identities\{ (find your own numbers) }\Software\Microsoft\Outlook Express\5.0\Compact Check Count"

'check if key exists
If RegKeyExists(myRegKey) = True Then

If RegKeyRead(myRegKey) > 95 Then
RegKeySave myRegKey, 1
End If
Else

MsgBox "Check Sub compactCheckCountRegistryChange. Outlook Express -Compact Check Count- registry key cannot be found."

End If

End Sub
(Anonymous) on November 4th, 2010 04:43 am (UTC)
Varieties
Given that this is creating a scripting shell, you could also just as easily call an instance of "reg.exe" and call it with whatever parameters suit your needs...

I'm also wondering whether this better or worse than declaring the advapi32.dll routines in VBA. both have portability issues...
( 3 comments — Leave a comment )