How to make VBA code compatible for the 2010 office - 64-bit and older office versions

Question

I found a problem with the below function calls when we migrated to the bit version of Office 2010-64.

Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

According to the information available at http://msdn.microsoft.com/en-us/library/ee691831.aspx . I modified the above call as shown below and it works fine on the 64-bit version of Office 2010.

Private Declare PtrSafe Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

The problem is that I need to make the same call to work with older versions of Office, and it throws a compilation error in older versions.

Does anyone know how to make this call work in office versions of Office 2010 and older.

Answer 1

As the MSDN article says, use conditional compilation: it works fine for me in Excel 97 through Excel 2010 32-bit and 64-bit.

#If VBA7 Then
Private Declare PtrSafe Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#Else
Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End if