Using Pre-Compiler Directives for Cross-Platform Compatibility of Your Excel VBA Programs

Need your single Excel program to run on Windows and Mac? With Office 32-bit and Office 64-bit? You can do that using pre-compiler directives * Here’s what you need to know (and code!)

In my Blog post from last week In which I elaborated on writing VBA code that runs on both Office 32-bit and Office 64-bit, we ended up with a challenge. We need to include the PtrSafe and LongPtr qualifiers in external libraries declarations for Office 64-bit, but ignore these qualifiers in Office 32-bit environments (where they are unknown).

Today I’ll explain all about VBA’s pre-compiler directives and how they solve our challenge easily.

Understanding Compilers

If you have already taken my Computer Programming with Excel VBA online course, you already know all about compilers. Here’s a quick summary.

Simply put, a compiler takes code in one language and outputs the program in another language. It’s a language translator.

Typically, compiled computer languages (such as C, C++ and Java) submit the machine a highly efficient code, in a language that is very close to the “hardware”, therefore highly performant and efficient. Before this binary program is handed over to the machine, it needs to be created. The compiler takes in your code and processes it into this binary program.

Not all computer languages are compiler-based, some are interpreter based, meaning, the same code you have written is passed over to the run-time engine for execution line by line, as it is. Your code is interpreted line by line as you have written it. Good examples here include Python and JavaScript.

Is VBA a Compiled or Interpreted Language?

This is a good question, why? Because it does not have a clear yes or no answer…

VBA is kind of a hybrid in which the code you write is compiled, however retains its original structure so that the statements are executed and traced to your original code.

This allows us to enjoy some benefits of both worlds: on one hand, a compiler run that will flag some well-described errors for us in advance, having to do with syntax, types, declarations, objects reference and compatibility issues. On the other hand, we can debug our code in step mode line by line, as each line is “interpreted” at runtime.

Another benefit of having a compiler run, is that we can instruct the compiler to ignore or include certain segments of our code, depending on various conditions we can test for during compilation time.

Can you already feel the solution to our challenge here?

VBA Pre-compiler directives

Let’s recall the declaration we left off with in last week’s Blog post:

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)

We sure have our PtrSafe and LongPtr qualifiers to render this statement valid for Office 64-bit. However, it will fail on Office 32-bit.

So, it seems we need two versions of the above declaration statement, one to feed 32-bit Office compilers and another to feed 64-bit Office compilers.

Luckily, we can employ the VBA pre-compiler directives that allows us to include/exclude statements in our code, based on specific conditions.

Consider the following VBA code:

#If VBA7 Then

 Declare PtrSafe Sub...

#Else

 Declare Sub...

#EndIf

These pre-compiler statements are processed before the compiler attempts to compile our code, eliminating any statement within a false clause.

In a 64-bit Office, the above code construct will be replaced with the following when serviced to the compiler:

Declare PtrSafe Sub...

In a 32-bit Office, the above code construct will be replaced with the following when serviced to the compiler:

Declare Sub...

VBA7 is a predefined constant, returning True if VBA7 (Office 2010 and up) is found. Before Office 2010, VBA6 was used.

Other predefined constants you can use to filter code to the compiler include:

VBA6 – always False on 64-bit Office and True on 32-bit Office with VBA6 or earlier.

Win64 – True if 64-bit Office (not Windows!) is found.

Mac – returns True on Apple Macs.

By the way, in addition to these predefined constants, you can also declare your own pre-compiler constants at the top of the module, as in the following example:

#Const TEST_MODE = 1

Then you can use to select parts of your code depending on your constant:

#If TEST_MODE Then

     Debug.Print …

#Else

     Range(“A1”).value = …

#End If

Connecting the Dots: calling external DLL functions in Excel VBA programs across different Windows platforms

If you consider the last two Blog posts together with this one, you have a complete guide to write robust, cross-Windows platforms Excel VBA programs, while leveraging external DLL functions.

The first Blog post in the bunch (from two weeks ago) detailed how to call external Windows DLL functions for making use of the vast Windows function libraries in your VBA program.

Last week we discussed in detail what differences you must consider in your code for different Windows/Office environments.

Today we implemented the required changes in code in your single VBA compatible program.