Conditionally Yours

VBA is a very simple
language and programming environment, very easy to get something up and running
very quickly, but it is showing its age these days, and with Microsoft making
no improvements to the language it will not get better.

Which all means that we
should utilise all of the functionality already available to us. I was using a
particular technique the other day, and I recollected a discussion on one of
the popular Excel forums that I had participated in, one in which I was surprised
at the lack of knowledge of this technique.

The situation I found
myself in was that I had developed some functionality in an application, and
this functionality was quite sophisticated, but was not fully working as I
wanted. I needed to deliver something, so I decided to do it in a simpler way,
but I didn’t want to just lose my previous efforts, I wanted to continue to try
and complete my initial attempts.

I could have just stripped
out all of the old code and added in the new code and save it as a new
workbook, I will still have the original code from the previous workbook. But
this would mean that any other changes that I made to my new workbook, other
than this revised function, would need to retro-fitted into the old workbook
otherwise it would soon be useless. I think the chances of messing this up are

Another way would be to
create a Public constant, and test this variable and execute the code depending
upon this variable, something like this

Public Const NewVersion As Boolean
= True

And then in the modules,
code such

    If Not
NewVersion Then

       ‘old code

        ‘new version code
    End If

This works okay, but of
course all of the code will be compiled, and there are potential problems if
you have variables associated specifically with the old or new versions, if
they are being used incorrectly elsewhere the compiler will not throw them out
as they will still be defined.

The solution is to use Conditional Compilation. This is similar
to the technique above, but a conditional constant is used, and conditional
statements. The code would look like this

#Public NewVersion = True

And then in the modules,
code such

    #If Not NewVersion Then

       ‘old code

        ‘new version code
    #End If

The advantages to this
approach are:

depending upon
the value of the conditional constant, only that part of the code will be

unlike standard
If statements, public variables can be wrapped in #If … #End If statements

VBA contains a
few built-in conditional constants, such as Mac (test for Mac platform), Win
(Windows), VB6, which are available to use

the conditional
statements can help ensure variables are correctly used when compiling

identifying the
code to strip out if and when it is decided that one approach is the preferred
way is facilitated by the #If statements.

I tend to put conditional
constants in each code module, although it is possible to set it in the project
properties. I just find the module approach more intuitive, you can assign
values such as True/False whereas in the project properties you have to set
values of -1/0 (sic!). It also means I can have different values in each module
when I am testing. Note that conditional constants will not accept an explicit
Public declaration, nor will they allow strict data typing.

This is an extremely
useful technique, but just remember to have those values properly set at the


5 thoughts on “Conditionally Yours

  1. I prefer maintaining multiple versions of modules as plain text .BAS files and using RCS to handle multiple versions.

    The only thing I use conditional compliation for is testing VBA6 around my own VBA5 implementations of InStrRev, Join, Split, etc work-alikes.

  2. That is a good approach Harlan, but because of the way I organise my code, some of my modules would contain code for both the old and new functionality, but also for other functionality. For instance, I have shared functions module, where I would have procedures that say setup CF in a range.

    I can’t remember the last time I had to do any Excel 97 work, when I last needed the VB6 conditional constant.

  3. I did not know about #if until someone mentioned it on DDOE a few years ago. I still don’t think I’ve used it. What’s the again of compiling code and not using it, over not compiling code and not using it?

    Having said that I have exactly the same issue as you Bob, where you want to come back to something. Some times having 2 versions does not makes sense, and I don’t think it’s a VBA/Excel issues, I have the same thing with VS projects.

  4. Ross,

    The advantages are that it is not compiled! So code that should not be executed CANNOT be executed.

    In the examples Hralan gave, where a version doesn’t support a particular piece of code, it is imperative, but I just find it so much more useful, it helsp me organise my code better.

Leave a Reply

Your email address will not be published. Required fields are marked *