VBA Programming Series...39

Controlling Program Flow and Making Decisions

Some VBA procedures start at the code’s beginning and progress line by line to the end, never deviating from this top-to-bottom program flow.

Macros that you record always work like this. In many cases, however, you need to control the flow of your code by skipping over some statements, executing some statements multiple times, and testing conditions to deter-mine what the procedure does next. Hang on to your hat and enjoy the ride, because you’re about to discover the essence of programming. Some programming newbies can’t understand how a dumb computer can make intelligent decisions. The secret is in several programming constructs that most programming languages support. 

Construct	How It Works
GoTo statement	Jumps to a particular statement.
If-Then structure	Does something if something else is true.
Select Case	Does any of several things, depending on some-
	thing’s value.
For-Next loop	Executes a series of statements a specified
	number of times.
Do-While loop	Does something as long as something else
	remains true.
Do-Until loop	Does something until something else becomes
	true.

The GoTo Statement

A GoTo statement offers the most straightforward means for changing a pro-gram’s flow. The GoTo statement simply transfers program execution to a new statement, which is preceded by a label.

Your VBA routines can contain as many labels as you like. A label is just a text string followed by a colon.

The following procedure shows how a GoTo statement works:

Sub GoToDemo()

UserName = InputBox(“Enter Your Name: “)

If UserName <> “Bill Gates” Then GoTo WrongName

MsgBox (“Welcome Bill...”)

‘   ...[More code here] ...

Exit Sub

WrongName:

MsgBox “Sorry. Only Bill Gates can run this.” End Sub

The procedure uses the InputBox function to get the user’s name. If the user enters a name other than Bill Gates, the program flow jumps to the WrongName label, displays an apologetic message, and the procedure ends. On the other hand, if Mr. Gates runs this procedure and uses his real name, the procedure displays a welcome message and then executes some additional code (not shown in the example). Notice that the Exit Sub statement ends the procedure before the second MsgBox function has a chance to work.

This simple routine works, but VBA provides several better (and more struc-tured) alternatives than GoTo. In general, you should use GoTo only when you have no other way to perform an action. In real life, the only time you must use a GoTo statement is for trapping errors. 

Many hard-core programming types have a deep-seated hatred for GoTo state-ments because using them tends to result in difficult-to-read (and difficult-to-maintain) “spaghetti code.” Therefore, you should avoid this subject when talking with other programmers.

Decisions, decisions

Now, I discuss two programming structures that can empower your VBA procedures with some impressive decision-making capabilities: If-Then and Select Case.

The If-Then structure

Okay, I’ll say it: If-Then is VBA’s most important control structure. You’ll probably use this command on a daily basis (at least I do). As in many other aspects of life, effective decision-making is the key to success in writing Excel macros. 

that a successful Excel application boils down to making decisions and acting upon them.

The If-Then structure has this basic syntax:

If condition Then statements [Else elsestatements]

Use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, if included, lets you execute one or more statements if the condition you’re testing is not true. Sound confusing? Don’t worry; a few examples make this crystal clear.

If-Then examples

The following routine demonstrates the If-Then structure without the optional Else clause:

Sub GreetMe()

If Time < 0.5 Then MsgBox “Good Morning”

End Sub

The GreetMe procedure uses VBA’s Time function to get the system time. If the current system time is less than .5 (in other words, before noon), the routine displays a friendly greeting. If Time is greater than or equal to .5, the routine ends and nothing happens.

To display a different greeting if Time is greater than or equal to .5, add another If-Then statement after the first one:

Sub GreetMe2()

If Time < 0.5 Then MsgBox “Good Morning”

If Time >= 0.5 Then MsgBox “Good Afternoon”

End Sub

Notice that I used >= (greater than or equal to) for the second If-Then state-ment. This ensures that the entire day is covered. Had I used > (greater than), then no message would appear if this procedure were executed at precisely 12:00 noon. That’s pretty unlikely, but with an important program like this, we don’t want to take any chances.

An If-Then-Else example

Another approach to the preceding problem uses the Else clause. Here’s the same routine recoded to use the If-Then-Else structure:

Sub GreetMe3()

If Time < 0.5 Then MsgBox “Good Morning” Else _

MsgBox “Good Afternoon”

End Sub

Notice that I use the line continuation character (underscore) in the preced-ing example. The If-Then-Else statement is actually a single statement. VBA provides a slightly different way of coding If-Then-Else constructs that use an End-If statement. Therefore, the GreetMe procedure can be rewritten as:

Sub GreetMe4()

If Time < 0.5 Then

MsgBox “Good Morning”

Else

MsgBox “Good Afternoon”

End If

End Sub

In fact, you can insert any number of statements under the If part, and any number of statements under the Else part. I prefer to use this syntax because it’s easier to read and makes the statements shorter.

What if you need to expand the GreetMe routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, the three statements, is simplest:

Sub GreetMe5()

Dim Msg As String

If Time < 0.5 Then Msg = “Morning”

If Time >= 0.5 And Time < 0.75 Then Msg = “Afternoon” If Time >= 0.75 Then Msg = “Evening” MsgBox “Good “ & Msg

End Sub

The Msg variable gets a different text value, depending on the time of day. The final MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.

The following routine performs the same action but uses an If-Then-End If structure:

Sub GreetMe6()

Dim Msg As String

If Time < 0.5 Then

Msg = “Morning”

End If

If Time >= 0.5 And Time < 0.75 Then

Msg = “Afternoon”

End If

If Time >= 0.75 Then

Msg = “Evening”

End If

MsgBox “Good “ & Msg

End Sub

Using ElseIf

In the previous examples, every statement in the routine is executed — even in the morning. A more efficient structure would exit the routine as soon as a condition is found to be true. In the morning, for example, the procedure should display the Good Morning message and then exit — without evaluat-ing the other superfluous conditions.

With a tiny routine like this, you don’t have to worry about execution speed. But for larger applications in which speed is important, you should know about another syntax for the If-Then structure. The ElseIf syntax follows:

If condition Then

[statements]

[ElseIf condition-n Then

[elseifstatements]]

[Else

[elsestatements]]

End If

Here’s how you can rewrite the GreetMe routine by using this syntax:

Sub GreetMe7()

Dim Msg As String

If Time < 0.5 Then

Msg = “Morning”

ElseIf Time >= 0.5 And Time < 0.75 Then

Msg = “Afternoon”

Else

Msg = “Evening”

End If

MsgBox “Good “ & Msg

End Sub

When a condition is true, VBA executes the conditional statements and the If structure ends. In other words, VBA doesn’t waste time evaluating the extraneous conditions, which makes this procedure a bit more efficient than the previous examples. The trade-off (there are always trade-offs) is that the code is more difficult to understand. (Of course, you already knew that.)

Another If-Then example

Here’s another example that uses the simple form of the If-Then structure. This procedure prompts the user for a quantity and then displays the appro-priate discount, based on the quantity the user enters:

Sub ShowDiscount()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity:”)

If Quantity > 0 Then Discount = 0.1

If Quantity >= 25 Then Discount = 0.15

If Quantity >= 50 Then Discount = 0.2

If Quantity >= 75 Then Discount = 0.25

MsgBox “Discount: “ & Discount

End Sub

Notice that each If-Then statement in this routine is executed, and the value for Discount can change as the statements are executed. However, the rou-tine ultimately displays the correct value for Discount because I put the If-Then statements in order of ascending Discount values.

The following procedure performs the same tasks by using the alternative ElseIf syntax. In this case, the routine ends immediately after executing the statements for a true condition.

Sub ShowDiscount2()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity: “)

If Quantity > 0 And Quantity < 25 Then

Discount = 0.1

ElseIf Quantity >= 25 And Quantity < 50 Then

Discount = 0.15

ElseIf Quantity >= 50 And Quantity < 75 Then

Discount = 0.2

ElseIf Quantity >= 75 Then

Discount = 0.25

End If

MsgBox “Discount: “ & Discount

End Sub

Personally, I find these multiple If-Then structures rather cumbersome. I generally use the If-Then structure for only simple binary decisions. When a decision involves three or more choices, the Select Case structure offers a simpler, more efficient approach.

The Select Case structure

The Select Case structure is useful for decisions involving three or more options (although it also works with two options, providing an alternative to the If-Then-Else structure).

The syntax for the Select Case structure follows:

Select Case testexpression

[Case expressionlist-n

[statements-n]] . . .

[Case Else

[elsestatements]]

End Select

Don’t be scared off by this official syntax. Using the Select Case structure is quite easy.

A Select Case example

 

The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in the previous section:

Sub ShowDiscount3()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity: “)

Select Case Quantity

Case 0 To 24

Discount = 0.1

Case 25 To 49

Discount = 0.15

Case 50 To 74

Discount = 0.2

Case Is >= 75

Discount = 0.25

End Select

MsgBox “Discount: “ & Discount

End Sub

In this example, the Quantity variable is being evaluated. The routine is checking for four different cases (0–24, 25–49, 50–74, and 75 or greater).

Any number of statements can follow each Case statement, and they all are executed if the case is true. If you use only one statement, as in this example, you can put the statement on the same line as the Case keyword, preceded by a colon — the VBA statement separator character. In my opinion, this makes the code more compact and a bit clearer. Here’s how the routine looks, using this format:

Sub ShowDiscount4 ()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox(“Enter Quantity: “)

Select Case Quantity

Case 0 To 24: Discount = 0.1

Case 25 To 49: Discount = 0.15

Case 50 To 74: Discount = 0.2

Case Is >= 75: Discount = 0.25

End Select

MsgBox “Discount: “ & Discount

End Sub

When VBA executes a Select Case structure, the structure is exited as soon as VBA finds a true case and executes the statements for that case.

A nested Select Case example

As demonstrated in the following example, you can nest Select Case struc-tures. This routine examines the active cell and displays a message describ-ing the cell’s contents. Notice that the procedure has three Select Case struc-tures and each has its own End Select statement.

Sub CheckCell()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Msg = “is blank.”

Case Else

Select Case ActiveCell.HasFormula

Case True

Msg = “has a formula”

Case False

Select Case IsNumeric(ActiveCell)

Case True

Msg = “has a number”

Case Else

Msg = “has text”

End Select

End Select

End Select

MsgBox “Cell “ & ActiveCell.Address & “ “ & Msg

End Sub

The logic goes something like this:

1. Find out whether the cell is empty.

2. If it’s not empty, see whether it contains a formula.

3. If there’s no formula, find out whether it contains a numeric value or text.

When the routine ends, the Msg variable contains a string that describes the cell’s contents. As shown in Figure 10-1, the MsgBox function displays that message.

You can nest Select Case structures as deeply as you need to, but make sure that each Select Case statement has a corresponding End Select statement.

If you’ve ever wondered why I indent the code I present here, the previous list-ing serves as a good example. The indentations really help to make the nesting levels clear (at least I think so). If you don’t believe me, take a look at the same procedure without any indentation:

Sub CheckCell()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Msg = “is blank.”

Case Else

Select Case ActiveCell.HasFormula

Case True

Msg = “has a formula”

Case False

Select Case IsNumeric(ActiveCell)

Case True

Msg = “has a number”

Case Else

Msg = “has text”

End Select

End Select

End Select

MsgBox “Cell “ & ActiveCell.Address & “ “ & Msg

End Sub