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

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 "Rt开发者_Python百科lMoveMemory" (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.

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

I find the use of this VBA7 compiler constant all over the Internet in relation to Office 64-bit compatibility but contrary to what is often said, this compiler constant detects Office installs using VBA7 e.g. Office 2010 onwards, not 64-bit Office. Confusingly, if you need to determine if you're using the 64-bit version of Office application, you need to use the Win64 constant!

Try this out of 32 and 64 bit versions of Office and you can see what I mean:

Sub DemoCompilerConstants()

    #If VBA7 Then
        MsgBox "VBA7"
    #Else
        MsgBox "Not VBA7"
    #End If

    #If Win64 Then
        MsgBox "Win64"
    #Else
        MsgBox "NOT Win64"
    #End If

End Sub

Thanks to Steve Rindsberg for putting me straight on this one! Steve also added:

Win64 actually tells you whether or not your version of your Office app is 64-bit. If you have to support older versions of Office, you may want to combine that with a VBA7 check to special-case versions of your Office app that won't understand the newer compiler directives. But if your code's failing on a 32-bit version of the Office app, it may well be because it's hitting your compiler directive, finding that yes, it's got VBA7 and then trying to exec the 64-bit API call, which won't fly because it's 32-bit Office.

So this should be the correct approach for combining VBA7 and 64-bit Office compatibility:

#If VBA7 Then
  #If Win64 Then ' Declare using PtrSafe for Office 64 bit
    Declare PtrSafe Function ....
  #Else ' Declare for Office 32 bit
    Declare Function ....
  #End If
#Else ' Declare for Office 32 bit
  Declare Function ....
#End If

Refining the excellent info/answer from Jamie Garroch (which explains how the VBA7 compiler constant doesn't tell you for sure if your code is running in a 64-bit Office application), the VBA compiler's #if directive can handle And operators.

That means you don't need to repeat your 32-bit function declarations. You can simply do this:

    #If VBA7 And Win64 Then ...
        '64 Bit Declarations: for example ...
        Private Declare PtrSafe Sub API_CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, source As Any, ByVal bytes As Long)
    #Else
        '32 Bit Declarations: for example ...
        Public Declare Sub API_CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, source As Any, ByVal bytes As Long)
    #End If

If you are using Office 2003, then using:

#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

Works but when you save your workbook and reopen it again, only the correspondong code to your OS is keeped. The #If then #Else and #End if have disappeared.