That Whacky Conditional Compiler Argument

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.

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.