[VBA] How to Create Macros Compatible with Both 32-bit and 64-bit Office (Vba7, Win64)

Since Excel 2010, Office has been available in both 32-bit and 64-bit versions. Consequently, VBA code—especially Declare statements that call Windows API functions—must be written differently depending on the bitness of the environment. If you write code that does not account for this, your macros may cause errors on other people’s computers.

VBA provides “conditional compilation,” which includes predefined special constants to automatically determine the user’s environment:

• Vba7: Checks if the VBA version is 7 or later (Excel 2010 or later).
• Win64: Checks if the running Office is the 64-bit version.

In this article, I will explain how to use these constants to create highly compatible macros that work correctly in both 32-bit and 64-bit versions of Excel.

Code for Compatibility Using Vba7 and Win64

When calling Windows API functions in the 64-bit version of Office (VBA7/Excel 2010 or later), you must add the PtrSafe keyword to the Declare statement.

The following sample code uses Vba7 to switch the Declare statement and Win64 to output the current execution environment to the Immediate Window.

Complete Code

'--- 1. Switch API function declaration using Vba7 ---
' Vba7 = Excel 2010 or later VBA
#If Vba7 Then
    ' Declare with PtrSafe considering 64-bit version
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    ' Declaration for older VBA (Excel 2007 or earlier)
    Declare Function GetTickCount Lib "kernel32" () As Long
#End If


' Check execution environment and call API function
Sub CheckEnvironmentAndRunAPI()

    '--- 2. Determine the bitness of the running Office using Win64 ---
    #If Win64 Then
        ' Process when running on 64-bit Office
        Debug.Print "This Excel is 64-bit."
    #Else
        ' Process when running on 32-bit Office
        Debug.Print "This Excel is 32-bit."
    #End If
    
    '--- 3. Call the API function ---
    ' GetTickCount returns the elapsed time in milliseconds since the PC started
    Debug.Print "Elapsed time since PC start: " & GetTickCount() & " ms"
    
End Sub

Explanation of the Code

1. Vba7 Conditional Compilation Constant

#If Vba7 Then: This condition becomes True if the VBA version executing the code is 7 or later (Excel 2010 or later).

• PtrSafe: This keyword is required to safely call API functions in a 64-bit environment.
• Since PtrSafe is not recognized in versions prior to VBA7, this #If block is used to branch the code so that Declare statements without PtrSafe are compiled in older environments.

This prevents syntax errors when opening the file in both old and new versions of Excel.

2. Win64 Conditional Compilation Constant

#If Win64 Then: This condition becomes True if the macro is running on the 64-bit version of Office. It is False for the 32-bit version.

Using this allows for finer control, such as handling data types that depend on bitness (like LongPtr) differently or notifying the user of their current environment.

Summary

In this article, I explained how to create compatible macros that work across different Excel environments using the conditional compilation constants Vba7 and Win64.

• #If Vba7 Then: Branches processing based on the VBA version (mainly distinguishing between Excel 2007 and earlier vs. 2010 and later). It is essential for switching the presence of PtrSafe in API declarations.
• #If Win64 Then: Branches processing based on the Office bitness (32-bit vs. 64-bit).

If you create macros that use Windows API functions and plan to distribute them to others, it is very important to use this technique to ensure no errors occur in various environments.