Category Archives: 12396

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 obvious.

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 end.