how to make VBA code compatible for office 2010 - 64 bit version and older office versions

Question

I have observed an issue with below function call when we migrated to office 2010-64 bit version.

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

According to information available on http://msdn.microsoft.com/en-us/library/ee691831.aspx link. I have changed above call as below and it has been working fine on office 2010 64 bit version.

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

The problem is, I need to make same call to work on older office versions as well and it throws compile error on older versions.

Has anyone any idea how to make this call working for office 2010 and older office versions.

Answer 1

As the MSDN article says, use conditional compilation: it works well for me in Excel 97 through Excel 2010 32-bit & 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