32-Bit / 64-Bit Architecture Conflicts and Compatibility in VBA
If you have ever deployed an Excel macro workbook that worked flawlessly on your machine, only for a coworker to get a flashing “Compile error in hidden module” message, you are not alone. As organizations gradually transition from 32-bit to 64-bit Office environments, VBA developers bear the brunt of the friction. Managing Excel VBA 32 bit / 64 bit compatibility can quickly turn into a debugging nightmare, especially when dealing with Windows API calls and external libraries.
Fortunately, understanding how the VBA compiler reads different architectures—and leveraging unified compiler workflows—can eliminate these errors entirely.
The Root Cause: Why VBA Breaks Between 32-Bit and 64-Bit
The fundamental conflict stems from memory addresses. In a 32-bit Excel environment, memory addresses are 32 bits long. In a 64-bit environment, they are 64 bits long.
When your VBA code declares a Windows API function using older 32-bit syntax, 64-bit Excel panics because it cannot safely fit a 64-bit memory address into a 32-bit data type. This triggers the notorious VBA PtrSafe 64 bit error: “The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and mark them with the PtrSafe attribute.”
If this error occurs within a protected or password-locked VBA project, the end-user simply sees a generic compile error in a hidden module 64 bit warning, leaving them unable to use your tool.
The Solution: Conditional Compilation and LongPtr
To fix this, you must use conditional compilation vba 64 bit directives. These directives act like If…Then statements, but instead of running during execution, they are evaluated by the compiler before the code even runs.
VBA provides a special attribute (PtrSafe) and a flexible data type (LongPtr) to bridge the gap and avoid the error on 64-bit Excel. The magic of LongPtr is that it automatically morphs: it acts as a 4-byte Long in 32-bit Excel, and an 8-byte LongLong in 64-bit Excel.
Here is the standard unified workflow syntax for declaring an API function (like GetTickCount) that works seamlessly across both architectures:
VBA
#If VBA7 Then ' Code is running in VBA 7 (Excel 2010 or newer, 32-bit or 64-bit) #If Win64 Then ' 64-bit Excel: Requires PtrSafe, returns a 64-bit LongPtr handle Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr #Else ' 32-bit Excel: Requires PtrSafe, returns a 32-bit LongPtr (which behaves like a Long) Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr #End If #Else ' Legacy Code for older Excel versions (VBA 6 and below) ' No PtrSafe allowed, window handles are strictly 32-bit Longs Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long #End If
Crucial Tip: Always use LongPtr for pointers and handles (like hWnd or hDC), but keep standard numbers, loops, and counters as a regular Long.
Mitigating Fragmentation with a Unified Compiler Workflow
Manually updating hundreds of API declarations across a complex corporate project is tedious and prone to human error. For large-scale VBA projects, relying on manual conditional compilation leads to target architecture fragmentation—where developers accidentally maintain two separate versions of the same file.
VBA Compiler compiles the workbook or add-in into a single solution that automatically matches the 32-bit or 64-bit architecture of Excel. With a selection of a single radio button option, you can target both 32 and 64 bit Excel!
By implementing strict conditional compilation and leveraging automated build tools, you can permanently eliminate hidden module errors and ensure your Excel tools run flawlessly on any machine, regardless of bitness—ensuring both 32 bit and 64 bit Excel VBA compatibility.