VBA Trick of the Week :: Conditional Compiling

Posted: June 26, 2013 by MaxFraudy in Excel, VBA
Tags:

In conditional compilation, particular blocks of code in a program are compiled selectively while others are ignored (msdn)

Where Can Conditional Compiling be Used?

There are many situations where you might want to ignore certain parts of code based on a condition. e.g. you may want to create different versions based on Excel version or local language settings or some other parameter. This can be done using conditional compiling. The syntax is as below:


Dim mVers   As String

Sub Init()

    #If Win64 Then
        mVers = "Win64" ' Win64=true, Win32=true, Win16= false
        Call VerCheck
    #ElseIf win32 Then
        mVers = "Win32"  ' Win32=true, Win16=false
        Call VerCheck
    #ElseIf win16 Then
        mVers = "Win16"  ' Win16=true
        Call VerCheck
    #End If

End Sub

Sub VerCheck()
    MsgBox "Version: " & mVers, vbInformation, "Version"
End Sub

Here, the variable mVers is being assigned a value at compile time based on whether it’s a 16-bit, 32-bit or a 64-bit environment. If it’s a 32-bit environment the variable will hold “Win32” value, if it’s 16-bit then “Win16”, otherwise “Win64”.

Similarly, you can conditionally compile blocks of code by checking for the values of other available compiler constants, the list of which can be found here

You could also define your own Compiler Constants as below:

Declaring Conditional Compiling Constants

Declaring Conditional Compiling Constants

Now, this variable can be used in a similar manner as before. Here, I have used this variable to define whether I want to work in Testing mode or Production mode. Based on the mode, I might want to compile only certain parts of code. For this, I’ll write the following code:

Dim mVers   As String

Sub Init()

    #If Testing = 1 Then
        mVers = "Testing"
        Call VerCheck
    #Else
        mVers = "Production"
        Call VerCheck
    #End If

End Sub

Sub VerCheck()
    MsgBox "Version: " & mVers, vbInformation, "Version"
End Sub

Addendum

In the above two examples, the conditional statements have been kept inside a routine, however, the best use of this is when used outside a procedure.

e.g. LongLong is a 64-bit integer available only in 64-bit Excel. You would also have to convert all the Long variables to LongLong variables or LongPtr variables to make your code compatible on both.

#If VBA7 And Win64 Then
    Dim lngL As LongLong
#Else
    Dim lngL As Long
#End If
 
Advertisements
Comments
  1. snb says:

    I think you misread the link you are referring to: the order in which the conditions are being setup is crucial:

    Sub M_snb()
      #If Win64 Then
         x = 64
        ' Win64=true, Win32=true, Win16= false
      #ElseIf Win32 Then
         x = 32
        ' Win32=true, Win16=false
      #ElseIf Win16 Then
         x = 16
        ' Win16=true
      #End If
    End Sub

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s