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.
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.
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?
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...
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:
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
Range(“A1”).value = …
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.
The in-depth course, Beyond Excel Boundaries with Databases & SQL, is now open.If you are serious about your career, or about understanding Databases, SQL and how to develop Excel VBA business-grade programs with Databases - this course is definitely for you.