Convert VBA Declare Compatible with 64 Bit Office

Convert Excel VBA 32 Bit to 64 Bit

To solve Compile error: ‘The code in this project must be updated for use on 64-bit systems’, continue reading

If you upgrade your Excel to version higher than 2007 (i.e., Office 2010, 2013, 2016 or any higher version) & execute your VBA code from an earlier version of Office prior to 2010, then you will get error message like the above one.

It is because the VBA 6 or earlier versions default to a 32 bit system. So, the data processing will be targeting 32 bit data type operations. When you execute a older VBA code in 64 bit machine, the 64 bit or 8 byte data will be posted to 32 bit data types, which is not compatible. This will result in a error.

VBA Declare for 32 bit (VB6) & 64 bit (VB7)

To solve this, we have to explicitly, verify whether the current system is 64 bit or VBA7. If so, then change the declarations that are compatible for 64 bit. This can be done by declare PtrSafe 64 bit compatible command.

For Example, if we need to declare any API function, include the declare statements with the keyword PtrSafe keyword as mentioned below. Please note that this code will result in error message in VB6 editors. Read further to know how to solve that too.

'VBA code fix to make it compatible with 64-bit & 32-bit system
'Declare Windows API function in 64-bit Office
'Fix for Error message: The code in this project must be updated for use on 64-bit systems
#If Vba7 Then
    ' New VBA7 editor
     #If Win64 Then
        'Microsoft Office - 64 bit version
        Public Declare PtrSafe Function GetWindow Lib "USER32" (ByVal hWnd As LongPtr, ByVal wCmd As Long) As LongPtr
     #Else
        'Microsoft Office - 32 bit version
        Public Declare Function GetWindow Lib "USER32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
    #End If
#Else
    'VBA version 6 or earlier
    Public Declare Function GetWindow Lib "USER32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
#End If

Issue with VB6 Editor with PtrSafe: If this code is copied to a VB6 editor, then PtrSafe will not be identified as keyword in VB6. So the editor will give error message as “Expected Sub or Function”.

Solution: Just ignore this error. Editor will mark this line in red with warning. But the code will still execute. You will not be able to find any solution to solve this warning error.

Additional Reference: Microsoft Library Reference