Tutorial: Conditionals - If-then-else, Immediate If, Select Case

A hundred years ago, when I studied programming, my instructor taught us that all of programming could be summed up by four kinds of operations:

- Assignment: assigning a value to a variable;
- Branching: determining the flow of the logic based on a conditional test;
- Looping: repeating the same block of steps until some defined stopping condition;
- Formatting: manipulating strings, numbers (Math) and dates.

That's still basically true, although it has been much more finely divided, with the introduction of object oriented programming, etc.

In this tutorial, I will try to address CONDITIONALS in Microsoft Visual Basic for Applications (VBA).

VBA has several alternative syntaxes for conditionals. They are largely interchangeable; that is, you can usually accomplish the same result in more than one way, but often one method will be simpler than others.

The most well known conditional is the IF-THEN-ELSE syntax. In its simplest form it is: The Else block is optional.

If-Then statements can be nested: And so forth. Even beginning programmers should understand If-Then-Else structures.

As you can see, if you have more complex logic and required actions, this nesting could become rather complex, although, in theory, any logic could be represented in this way.

A short form syntax available in VBA is the "IMMEDIATE IF" function. In a single line of VBA code, you can assign a value based on several conditions. For example, to set the value of a commission rate variable, depending on the PositionLevel of the salesperson, you might do the following:
This results in the CommRate being set to .15 if the PosLevel equals 1, otherwise it is set to .20. The syntax is:

Iif( [condition - true or false], [value if true], [value if false] )

This can be expanded:

CommRate = Iif (PosLevel = 1, .15, Iif (PosLevel = 2, .2, Iif(PosLevel = 3, .25), 0)))

So if PosLevel=1 is true, CommRate will be .15; if not, and PosLevel=2, CommRate will be .2; if not, and PosLevel=3, CommRate will be .25; if not either 1, 2 or 3, CommRate will be 0.

Note the way the parentheses are positioned; beginners often have trouble with them in Immediate If statements. A quick check is to make sure you have the same number of opening and closing parentheses.

Immediate If is a compact syntax for assigning a value to a variable, but it can become hard to read if there are more than just a few valid choices, and it can only assign a value, it cannot execute a block of code, as If-Then-Else can.

Finally, VBA has a SELECT CASE structure, whereby the value of a single variable can be tested and the code to be executed for each condition can be determined, based on that value. The syntax is: That means that the variable named in the first line will be tested against the values in each succeeding Case: statement until the comparison is true, then the code in that block will be executed and it will skip over the other Case: statements, to the End Select. The Case Else: statement is optional.

An example would be a function written to determine the appropriate commission rate for a salesperson with a Job Category and a Monthly Sales amount. Let's say that commissions are based on this table:


You would want to calculate the appropriate commission amount by calling a function "GetRate()" with two arguments, like this:

Commission = GetRate(MoSales, JobCat) * MoSales

The function would look like this: This a very compact and understandable syntax, and fairly easy to read. It also returns zero commission if the JobCat is omitted or is invalid. Do you see why?

So this is an example of using two different conditional syntaxes together, to do a simple lookup. If the number of choices was high, it might be better to code this into a table, but for just 4 ranges of monthly sales and 3 job categories, it would be much simpler than setting up a table, and much faster in execution, since it doesn't have to access the table on the hard drive. It would, however, require editing the VBA code if the commission basis changed, so that would need to be taken into account as a factor in deciding how to code it.