Category Archives: 12395

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
    Else

        ‘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
    #Else

        ‘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
compiled

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