how to make VBA code compatible for office 2010 - 64 bit version 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