Summary
Conditional compilation allows us to control what code is compiled and run depending on environmental or situational sensitive attributes such as whether we are running on a Windows or Mac operating system, in a debugging or production mode, or running in a 32 or 64 bit environment. But the way conditional compilation directives function, built-in constants such as VBA7 and Win64 are presented as Boolean values but they are not which can lead to confusing results.
Details
Conditional Compilation
For a long time now, VBA for Office has provided support for conditional compilation. Conditional compilation lets you selectively compile specific lines of code. Below are some reasons for doing this.
- Your application needs to run in different versions of Office and there are differences in the object model between the versions, and code that may compile in one version will not in another.
- Your application needs to run in both 32 bit and 64 bit Office.
- You want to develop using early binding to get the benefits of VBE's Intellisense but you want to distribute code that uses late binding to avoid having to ship with library references and thus avoid library version issues.
- You want to distribute a testing version of your application with ample debugging code in place to detect and log status information and anomalous behavior, but want to distribute a production version that excludes the majority of debugging code to optimize performance.
Without conditional compilation, doing the above means either having different code projects or commenting and uncommenting a lot of code to create the different versions. The advantage of conditional compilation is that all versions of the code can co-exist in the same project. Conditionally compiled code is encapsulated in special “#If…#Else…#End If” directives where the “#If” directive uses one or more conditional compilation constants to determine whether or not the code within the “#If…#End If” is compiled. Note that the “#If” directive is not the same as the compiled “If” statement—all conditional compilation directives are preceded with a pound sign or hash tag.
When evaluating a conditional compilation constant with a “#If” directive, if the constant has not been defined then the test will not fail but always return a negative result. This makes conditional compilation directives work across all versions of Office, and allows us to conditionally use code that works in one version of Office where the constant is defined and not use that code in another version of Office where the constant is either not defined or is defined as 0.
Conditional Compilation Constants
A conditional compilation constant is a named “variable” set to an integer value. The most commonly used values are 0 (False) and -1 (True). Conditional compilation constants are defined one of three ways:
- In the “Project Properties” dialog box. When defined here the constant is available in all modules in the project.
- In the command line when the workbook is opened. When defined here the constant is available in all modules in the project.
- Using the “#Const” directive in a code module. When defined this way the constant is only available in the module in which it is declared.
More detailed information on how to define conditional compilation constants is presented here.
VBA provides some built in conditional compilation constants that allow conditional compilation determinant on environmental characteristics such as whether the installation is 32 or 64 bit or if the installation is Windows or Mac. A description of these constants is presented here.
Using Conditional Compilation
Besides the basic mechanics of the “#If…#Else…#End If” directives and defining the conditional compilation constants, how the #If evaluates the constants is where this discussion gets interesting. If you are not careful, the results may be unexpected. And the results are not always obvious since the condition determines what is compiled, not how the code functions when run.
What, Exactly, Is a Conditional Compilation Constant?
This is where things start to get confusing. The conditional compilation constant is essentially an undefined type. When entered using the “Project Properties” dialog it has to be an integer from -32768 to 32767 (no thousands separators.) When defined using the “#Const” directive it can be anything such an integer of any size, a real, a Boolean (True or False,) or a string.
Further, when using the “Project Properties” dialog to define conditional compilation constants, any value less than -9999 is saved but is not restored in the text edit field when the dialog is re-displayed.
The Boolean Test
The Boolean test is where conditional compilation really breaks down. Most systems including Excel formulas on the worksheet and VBA code evaluate Boolean tests as an examination of a numeric value (strings generate an error) where a value of 0 is False and any non-zero value is True. Effectively any value equal to 0 is False and any value not equal to 0 is True.
But the VBA conditional compilation function does not operate this way. It evaluates any value not equal to 0 as True and any value not equal to -1 as False. What this means is that any value other than 0 or -1 is going to result in a positive outcome whether or not the “Not” operator is used.
Why Is The Faulty Boolean Test An Issue?
The conditional compilation Boolean test is only an issue if the constant is presented as a Boolean value and you evaluate it as a Boolean in a test condition. Unfortunately, this is the case with all predefined constants: Microsoft defined them as set to a 0 or a 1. Let’s look at a specific example using the Win64 predefined constant
#If Win64 Then
Debug.Print "Is Win64"
#End If
#If Not Win64 Then
Debug.Print "Is not Win64"
#End If
If the conditional compilation comparison function operated as expected, if Win64 is 0 then “Win64” will be printed and if Win64 is 1 then “Not Win64” will be printed. But this is not the case. If Win64 is 0 then “Not Win64” will print and if Win64 is 1 then both “Win64” and “Not Win64” will print. Effectively, the “Not” condition will always be true because the test is “If Win64 <> -1” versus the correct “If Win64 = 0”.
Workarounds
There are four workarounds to deal with this deficiency.
- Don’t use the “Not” operator when working with built-in constants. Rather, use the “#Else” clause:
#If Win64 Then
Debug.Print "Is Win64"
#Else
Debug.Print "Is not Win64"
#End If
Or, if only the negative is needed:
#If Win64 Then
#Else
Debug.Print "Is not Win64"
#End If
- Use the negation operator:
#If –Win64 Then
Debug.Print "Is Win64"
#End If
#If Not –Win64 Then
Debug.Print "Is not Win64"
#End If
Note that the first case isn’t really needed but is included for consistency.
- Use a non-Boolean test:
#If Win64 = 0 Then
Debug.Print "Is not Win64"
#End If
#If Not Win64 = 0 Then
Debug.Print "Is Win64"
#End If
#If Win64 = 1 Then
Debug.Print "Is Win64"
#End If
#If Not Win64 = 1 Then
Debug.Print "Is not Win64"
#End If
- Define a new constant:
#Const MyWin64 = -Win64
#If MyWin64Then
Debug.Print "Is Win64"
#End If
#If Not MyWin64 Then
Debug.Print "Is not Win64"
#End If
Caveat: “#Const” defined constants are not global.
Dear Microsoft
In an ideal world, the built-in constant values would be a true Boolean of 0 or -1 instead of 0 or 1. However, given that the incorrect values have been in place for a while, changing this now would cause more problems that it would solve.
To address the issue discussed above, changing how the conditional compilation engine evaluates the “Not” operator would suffice. Currently it uses the logic “If Value <> -1”. The correct test is “If Value = 0”.
In addition, because the number of object model changes is increasing with the releases, we are in need of built-in conditional compiler constants that reflect the release version. As with the bit version, the release version would be forward but not backward compatible. Office15 would be “True” in 2013. Office14 would be “True” in 2010, 2013, and Mac 2011. Office12 would be “True” in 2007, 2010, and 2015.
Alternatively, a single constant can be used to represent the version number. For example: OfficeVersion = 15.0. This technique eliminates the need to create more constants for each release. And provides all of the functionality needed to implement conditional compilation based on version-dependent functionality and features.
A Final Note on 64 Bit Business
There is some confusion about how to use conditional compilation constants to implement code that functions in both 32 and 64 environments. There are two constants used for this:
VBA7: VBA7 is set to 1 if the code is running in Office 2010 or later, 0 for older versions.
WIN64: WIN64 is set to 1 if the Office installation is 64 bit, 0 for 32 bit.
What is important to understand when beginning the conversion of code to operate in both 32 and 64 bit environments is that VBA does the conversion for us in a single declaration. All we need to do is tell VBA that the declaration is ready for conversion. For example, take this Windows API declaration in a pre-64 bit world:
Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long
Changing the declaration to be convertible to either 32 or 64 bit it becomes:
Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As LongPtr
The PtrSafe keyword tells the compiler that the Declare statement is safe to run in 64-bit versions of Microsoft Office. The LongPtr type is converted by VBA at compile time to either a Long if running in a 32 bit environment or a LongLong in a 64 bit environment. (If defining a 64 bit integral variable then explicitly use the LongLong type. Note that the LongLong type is only available in 64 bit environments and should be used only when a Windows API routine specifically requires that type such as when calling a math API routine. In general you should not have to use the LongLong type.)
So which conditional compilation constant should we use and how? The answer lies in understanding that all we need to do is be aware that the VBA compiler might not understand the keywords PtrSafe and LongPtr. In other words, we might be running in a pre-2010 version environment. Therefore we need to use the VBA7 constant as follows:
#If VBA7 Then
Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As LongPtr
#Else
Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) _
As Long
#End If
The code in the first part of the “#If” construct is used in 2010 and later versions and VBA handles the task of ensuring it is properly configured for the bit environment in which the code is running. The second part of the “#If” construct is used in 2007 and earlier versions where 64 bit environments do not exist.
So when do we use Win64? Only when we explicitly need to write code that is different depending on the bit environment in any 2010 or later version.