Accessing Windows Functions

There is currently an example of how to access the registry on page 290 (Chapter 13) of VBA for Dummies. It will still work when working with the 32-bit version of Office 2010 and VBA 7.0albeit unreliably. This example won’t work with the 64-bit version of Office 2010 and could, in fact, cause problems in some cases (although I haven’t personally been able to produce something to show you, the fact remains that pointers are inherently dangerous and should be used with caution).

The registry access code shown in Listing 13-5 is used by the AccessAnObject() macro in Listing 13-6 on page 292. This macro will work fine after you make changes to the RegistryFuncs module. Here’s the current RegistryFuncs code.

' This Windows API function opens a registry key.
Public Declare Function RegOpenKey _
    Lib "advapi32.dll" _
    Alias "RegOpenKeyA" (ByVal HKey As Long, _
                         ByVal lpSubKey As String, _
                         phkResult As Long) As Boolean
 
' Use this enumeration for the top level keys.
Public Enum ROOT_KEYS
    HKEY_CLASSES_ROOT = &H80000000
    HKEY_CURRENT_USER = &H80000001
    HKEY_LOCAL_MACHINE = &H80000002
    HKEY_USERS = &H80000003
    HKEY_PERFORMANCE_DATA = &H80000004
    HKEY_CURRENT_CONFIG = &H80000005
    HKEY_DYN_DATA = &H80000006
End Enum
 
' This Windows API function reads a value from a key.
Declare Function RegQueryValue _
    Lib "advapi32.dll" _
    Alias "RegQueryValueA" (ByVal HKey As Long, _
                            ByVal lpSubKey As String, _
                            ByVal lpValue As String, _
                            lpcbValue As Long) As Boolean
 
' This Windows API function closes a registry key.
Public Declare Function RegCloseKey _
    Lib "advapi32.dll" (ByVal HKey As Long) As Boolean

Notice that these functions all use a Long data type to hold the pointer returned from the function calls. A Long isn’t the same as a pointer, but because there weren’t any pointer types before VBA 7.0 a Long was the best you could hope to achieve. VBA 7.0 does support pointers and you can see the list of them in the Application Programming Interface Compatibility topic on MSDN.

The correct replacement for the pointers in this example is a LongPtr. The LongPtr type works equally well with both 32-bit and 64-bit versions of Office. Of course, earlier versions of VBA don’t support the LongPtr type, so you need some way to work in both environments, which was the purpose of the Checking the VBA Environment post. Consequently, the new code looks like this:

' Use this enumeration for the top level keys.
Public Enum ROOT_KEYS
    HKEY_CLASSES_ROOT = &H80000000
    HKEY_CURRENT_USER = &H80000001
    HKEY_LOCAL_MACHINE = &H80000002
    HKEY_USERS = &H80000003
    HKEY_PERFORMANCE_DATA = &H80000004
    HKEY_CURRENT_CONFIG = &H80000005
    HKEY_DYN_DATA = &H80000006
End Enum
     
#If VBA7 Then
 
    ' This code replaces the Long values used in
    ' previous versions of VBA with LongPtr values.
    ' This Windows API function opens a registry key.
    Public Declare Function RegOpenKey _
        Lib "advapi32.dll" _
        Alias "RegOpenKeyA" (ByVal HKey As LongPtr, _
                             ByVal lpSubKey As String, _
                             phkResult As Long) As Boolean
     
    ' This Windows API function reads a value from a key.
    Declare Function RegQueryValue _
        Lib "advapi32.dll" _
        Alias "RegQueryValueA" (ByVal HKey As LongPtr, _
                                ByVal lpSubKey As String, _
                                ByVal lpValue As String, _
                                lpcbValue As LongPtr) As Boolean
     
    ' This Windows API function closes a registry key.
    Public Declare Function RegCloseKey _
        Lib "advapi32.dll" (ByVal HKey As LongPtr) As Boolean
 
#Else
 
    ' This Windows API function opens a registry key.
    Public Declare Function RegOpenKey _
        Lib "advapi32.dll" _
        Alias "RegOpenKeyA" (ByVal HKey As Long, _
                             ByVal lpSubKey As String, _
                             phkResult As Long) As Boolean
     
    ' This Windows API function reads a value from a key.
    Declare Function RegQueryValue _
        Lib "advapi32.dll" _
        Alias "RegQueryValueA" (ByVal HKey As Long, _
                                ByVal lpSubKey As String, _
                                ByVal lpValue As String, _
                                lpcbValue As Long) As Boolean
     
    ' This Windows API function closes a registry key.
    Public Declare Function RegCloseKey _
        Lib "advapi32.dll" (ByVal HKey As Long) As Boolean
 
#End If

Please contact me if you have any trouble with this code at John@JohnMuellerBooks.com. Also, be sure to keep letting me know about your Office 2010 needs!