In my first post on the subject, VBA – Early Binding and Late Binding, I went over the basics, but I thought I’d push forward a little more to help demonstrate how to implement “The Best of Both World”.

As I explained, in my first article, the best of both worlds consists of developing using early binding, but deploying using late binding. The issue becomes needing to change the procedure declarations between each technique. Thankfully, Conditional Compilation Directives (CCDs) can solve this dilemma very easily.
 

What are Conditional Compilation Directives?

Conditional Compilation Directives, in this instance, is an approach in which we can get our VBA program to compile one sequence, or another, depending on a condition of our choosing.

In our case, this directly equate to compiling one set of declarations when we want Early Binding, and compiling another set of declarations when we want Late Binding.
 

How is This Done?

Actually, using Conditional Compilation Directives is surprisingly easy, once you are aware of it and it can be very useful far beyond the Early/Late Binding issue.

The Basic Concept of Conditional Compilation is:

    ' #Const EarlyBind = True 'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oOutlook          As Outlook.Application
        Dim oOutlookMsg       As Outlook.MailItem
    #Else
        'Late Binding Declaration/Constants
        Dim oOutlook          As Object
        Dim oOutlookMsg       As Object
        Const olMailItem = 0
    #End If

In the above we declare a Constant EarlyBind and depending on its value our program will choose to use one set of declaration or another. So when we’d be developing our program, we set our constant to

#Const EarlyBind = True

and include the necessary Reference Library, in the example above Microsoft Outlook XX.X Object Library to benefit from Intellisence during development and access to help, object browser, … .

On the other hand, when deploying our program to the client/end-users, we would remove the reference library and switch the constant to

#Const EarlyBind = False

to eliminate any versioning issues.

The Best of Both Worlds, you see!
 

Be Aware

Use of the Hashtag

Notice that in the example provide above both the IF and the Const are preceeded by a #. This is critical! This is what changes regular code into compiler directives and without it your code will not work as intended.

Declaration of the Compiler Constant

You may be tempted to simply include the #Const … directly in your procedure, but as a general rule you’d be much better served to declare it at a module level so it applies throughout your entire module rather than each procedure being independent from one another. This will make development easier as you will have but one value to alter instead of needing to do so for every procedure.

If you do go the route of declaring #Const in each procedure in the same module, you will need to ensure they all have unique names or else you will get compilation errors:

Compile error:
Duplicate definition

What Else Can Conditional Compilation Do For Me?

Beyond the aspect of Early and Late Binding for which Conditional Compilation Directives (CCD) can be very useful, CCDs are also extremely useful now that we are facing different bitnesses of our Office installations requiring different API declarations. As such, instead of trying to juggle different sets of APIs declarations, it can simply be easier to use CCDs to build a single declaration which will work in all scenarios, something like

    #If VBA7 And Win64 Then
        'x64 Declarations
    #Else
        'x32 Declarations
    #End If

A Concrete Example

An extremely common API is the apiGetUserName which we often use in the function fOSUserName to get the current user’s OS Username. The above function works great in x32 version of Office, but crashes in x64. So let’s put to use our new knowledge of CCDs to build a universal API call that will work in both scenarios.

#If VBA7 And Win64 Then
    'x64 Declarations
    Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#Else
    'x32 Declaration
    Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If

Function fOSUserName() As String
    ' Returns the network login name
    Dim lngLen As Long, lngX  As Long
    Dim strUserName           As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = GetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

You’ll also notice in the above example, we are not defining a #Const. VBA7, Win64 and others are defined by the system at runtime and this is what permits us to build such CCDs to make our code adaptive.

You can find a full list of all the available Compiler constants at:

Compiler constants (VBA)

Visual Basic for Applications defines constants for exclusive use with the #If...Then...#Else directive. These constants are functionally equivalent to constants defined with the #If...Then...#Else directive except that they are global in scope;

A Global Compiler Constant

Sadly, we cannot use keywords like Public, Private, … with such constants. When we declare a constant it has a module level scope (even when created in a procedure).

Thus, you must re-declare it in every module in which you need to use it. This is why I will try hard to group all my WMI procedures in one module, my WIA in another, … so I can have such a constant at the top that I switch and all my procedures switch.

If you truly want a project level (global) constant, there is one way to do so and that it to define the constant in the Project Properties dialog Conditional Compilation Arguments text box.

In the VBA Editor (VBE):

1. From the main toolbars, click on Tools
2. Click on Database Properties …
3. On the General tab, enter the constant and its value in the Conditional Compilation Arguments text box. If you want to input multiple values simply separate them by a colon (:).

So, say we wanted to take my ‘#Const EarlyBind = True’ example and make it apply project wide, we would simply enter:

EarlyBind = -1

and if we wanted to enter a couple constants, we would enter:

WMI_EarlyBind = 0 : WIA_EarlyBind = -1

The rest of the code, the way we check and use constants in your procedures … remains unchanged.

Should you ever wish to automate these values in some way, note that you can read the current value of the Conditional Compilation Arguments by doing:

Debug.Print Application.GetOption("Conditional Compilation Arguments")

and you can update the value by doing:

Call Application.SetOption("Conditional Compilation Arguments", "Your New Value ...")

Overriding Project Level Constants

If you declare a Project level constant and also declare it in a procedure, the procedure level declaration will reign supreme. This can be very useful during development/testing.

I’d like to thank both AHeyne and John (see the comments section below) as a recent discussion on the matter helped enlighten me so I could expand this article to include this latest section.
 

Useful References

Using Conditional Compilation

Conditional compilation lets you selectively compile certain parts of the program. You can include specific features of your program in different versions, such as designing an application to run on different platforms, or changing the date and currency display filters for an application distributed in several different languages.

Compiler Directives

There may be situations where we want to compile different forms of code, but not have multiple copies of essentially the same database and modules. Compiler Directives allow us to determine which code segment should be compiled.

Because Compiler Directives are compiled conditionally, we are afforded the opportunity to include code in our project that would be illegal in non-directive code.

Declaring API functions for 64 bit Office (and Mac Office)

If you develop VBA code for multiple versions of Office, you may face a challenge: ensuring your code works on both 32 bit and 64 bit platforms.

This page is meant to be the first stop for anyone who needs the proper syntax for his API declaration statement in Office VBA.

Office Talk: Working with VBA in the 32-bit and 64-bit Versions of Office 2010

Learn to use Microsoft Visual Basic for Applications code in the 32-bit and 64-bit versions of Microsoft Office