Writing Excel VBA Code That Runs on Office 32-bit and 64-bit

Your Excel 2016 program runs perfect on your Excel 2016 32-bit, but fails on Excel 2007, or on Excel 2013 64-bit. Why that happens and how to write a fully compatible VBA program? Here are the answers (and some VBA code)

In last week’s Blog post, I explained and demonstrated how Windows functions libraries, or DLLs, can be called from VBA.

Leveraging external functions to Excel is great, especially considering the vast variety of functions the Windows operating system offers. However, breaking the tight Excel environment is also subject to some compatibility issues.

Why are there compatibility issues when running Excel VBA code on different machines?

Over time, the Windows operating system has evolved. Some core changes in its architecture inevitably sent shock waves throughout all programs designed to run on it.

Same goes for the MS-Office suite, of which Excel is a part of.

The most important changes are the introduction of the Windows 32-bit and the Office 32-bit (in 1994) and then Windows 64-bit and Office 64-bit (in 2010).

These improvements follow the architectural improvements in the underlying Hardware.

What are these 32-bit and 64-bit anyway?

As I explain and demonstrate in my flagship course: Computer Programming with Excel VBA, a bit is the atomic data-representation element in computing, with two possible states: 0 and 1.

In order to represent meaningful data (such as your name), many bits are combined together (8 bits are called a Byte) and each combination of their state is designated a specific meaning. For example, we can decide that a single Byte that looks like this: 00000011 represents the number 3.

You can see that all possible combinations using a single Byte (8 bits) ends pretty fast at 256, therefore we combine 2 Bytes together, giving us a total of 16 bits to play with. Now we can represent 65,536 unique characters (2 to the power of 16) in a space of two Bytes.

In computers architecture, data is arranged in chunks of memory spaces, each has a unique address by which it can be referenced. Such an address is a number, also known as a pointer.

The early computers allocated a single Byte for pointers to memory chunks. As memory was very small, 8-bits, or 256 unique addresses, were enough.

Two bytes (or 16 bit) were an improvement as memory space grew bigger, now allowing for an address space of 65,536 pointers.

In today’s 64-bit machines, we are offered an address space of continuous 2 to the power of 64 in size.

You can now understand that the whole chain: your VBA program, through Office, Windows and the underlying hardware – must agree on the same address space, so that all pointers to memory are propagated and interpreted correctly.

When should we expect incompatibilities in our Excel VBA programs to be raised?

Before Office 2010 was introduced, all VBA code was targeting 32-bit environments. The data type used for memory addresses was a Long type (that unsurprisingly supports up to 32-bit, or 4-Bytes, unique numbers).

To accommodate the 64-bit (8-Bytes) address space, a new data type was introduced: LongLong.

When attempted to run on newer, 64-bit environments, LongLong memory addresses of 64-bit are being truncated as they conform to the 32-bit limit of the Long data type.

In the opposite direction, declaring an updated LongLong data type for addresses in 64-bit environments, will fail when attempted to run on 32-bit environments, as the LongLong data type is not known.

This discussion is of no concern if your VBA code is not calling external function libraries, as each Office version is self-contained with no manual intervention required.

The problem arises when you declare your intention to call an external function library, typically a DLL registered with Windows, in which case the declaration in your VBA code must comply with the functions’ signature in that DLL. If you’re not comfortable with what I just said – read my last week’s Blog post about calling DLLs from Excel VBA.

Writing compatible VBA code for both 32-bit and 64-bit environments

In order to bridge across the two environments, Microsoft introduced some new features with Office 2010:

The LongPtr type. A declaration of an external function (within a DLL) or its passed arguments with the LongPtr type, signals VBA to automatically adjust the data type declaration to resolve into either a Long or a LongLong pointer, depending on the Office version identified in runtime.

The LongPtr is not a data type in itself then, but rather a decision gate that will be replaced with the proper data type for us.

PtrSafe qualifier. adding the PtrSafe qualifier to a declaration, reassures the VBA compiler that the declaration is adjusted to run in 64-bit environment. This PtrSafe is needed when your code is targeting a 64-bit Office environment.

Taking the declaration to call the keyboard event function in the user32.DLL library from last week’s Blog post, you come up with:

Private Declare PtrSafe Sub keybd_event Lib "user32.dll" _

    (ByVal bVk As Byte, ByVal bScan As Byte, _

     ByVal dwFlags As LongPtr, ByVal dwExtraInfo As LongPtr)

As you can see, we added the PtrSafe qualifier to the declaration to signal the VBA compiler it is pointer-safe for 64-bit environments, and we indeed declared all pointers with LongPtr, so they are adjusted correctly to LongLong when run in 64-bit Office.

But there’s a catch. While the PtrSafe qualifier is required for 64-bit Office, it is not known in earlier, 32-bit Office. That means, introducing the statement as it is written up here will run on 64-bit Office only, and fail on 32-bit Office.

To overcome this hurdle, we need some way to tell the VBA compiler to selectively run either one of two different declaration statements, depending on the Office version identified in runtime: 32-bit or 64-bit Office.

Now you have a good reason to wait for next week’s Blog post in which I will cover VBA pre-compiler directives!