VBA Corner (vba_corner) wrote,

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
  '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

  '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:
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)
    '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."
      '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!

Tags: excel, ms_office, registry, vba, windows, windows_scripting, word
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded