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.

Ionogen

For your information: Microsoft recommends against using the 64-bit version of Office for compatibility reason. You should only use it if you need to process extremely large documents (Excel spreadsheets). – Cana

@0xA3 That does not seem to be an exact quote : technet.microsoft.com/en-us/library/ee681792.aspx, yesno? – Apospory

Perhaps conditional compiler constants : https://mcmap.net/q/1171958/-tempvars-and-access-2003/… #If Version = "14.0" Then Private Declare <...> – Apospory

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

Calmative

Thanks Charles, I tried this at my end but on 32 bit office it throws compilation error for PtrSafe stating "function or sub is not defined". Can you please let me know if I am missing anything. Many thanks in advance. – Ionogen

I just tested it on Excel 2003: compiles fine. – Calmative

The only thing I can think of is that I always develop under 32-bit and then test in 64: try exporting the code modules and re-import into 32-bit Excel – Calmative

This is now working at my end too. The onlything I did to get to work was, changed the setting to ignore syntactical errors. Thanks for all pointers Charles. – Ionogen

Answer 2

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

Transmarine

Thanks for the heads-up (RE the two compiler constants) Jamie. FYI VBA's #if directive is capable of evaluating multiple constants, so you can re-write what you wrote as #If VBA7 And Win64 Then ... (64 bit version) ... #Else ... (32 bit version) ... #Endif – Mukul

You do not need to use the Win64 constant to correctly declare API functions that will be compatible with all flavours of Office. You only need VBA7 for that. Yes, Win64 tells you the bitness of the Office, but you don't need that information to correctly declare APIs. – Erbe

Answer 3

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

Mukul

You do not need to use the Win64 constant to correctly declare API functions that will be compatible with all flavours of Office. You only need VBA7 for that. Yes, Win64 tells you the bitness of the Office, but you don't need that information to correctly declare APIs. – Erbe

Answer 4

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.

Hammack