Chapter 10

Controlling Program Flow and Making Decisions

In This Chapter

Discovering methods for controlling the flow of your VBA routines

Finding out about the dreaded GoTo statement

Using If-Then and Select Case structures

Performing looping in your procedures

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 determine what the procedure does next. Hang on to your hat and enjoy the ride, because you’re about to discover the essence of programming.

Going with the Flow, Dude

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. Table 10-1 provides a quick summary of these constructs. (I explain all of these later in this chapter.)

Table 10-1 Programming Constructs for Making Decisions

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 something’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 program’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 CheckUser()

    UserName = InputBox("Enter Your Name: ")

    If UserName <> "Steve Ballmer" Then GoTo WrongName

    MsgBox ("Welcome Steve...")

'   ...[More code here] ...

    Exit Sub

WrongName:

    MsgBox "Sorry. Only Steve Ballmer can run this."

End Sub

The procedure uses the InputBox function to get the user’s name. Then a decision is made: If the user enters a name other than Steve Ballmer, the program flow jumps to the WrongName label, displays an apologetic message, and the procedure ends. On the other hand, if Mr. Ballmer runs this macro 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. Without that Exit Sub statement, both MsgBox statements would be executed.

---

---

This simple routine works, but VBA provides several better (and more structured) 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. (I cover this in Chapter 12.)

By the way, I present the CheckUser procedure to demonstrate the GoTo statement. It is not intended to demonstrate an effective security technique!

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

Decisions, Decisions

As in many other aspects of life, effective decision-making is the key to success in writing Excel macros. If this book has the effect I intend, you’ll soon share my philosophy that a successful Excel application boils down to making decisions and acting upon them.

In this section, 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 come right out and 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).

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. Here’s the simple CheckUser procedure I presented earlier, recoded to use the If-Then-Else structure:

Sub CheckUser2()

    UserName = InputBox("Enter Your Name: ")

    If UserName = "Steve Ballmer" Then

        MsgBox ("Welcome Steve...")

'      ...[More code here] ...

    Else

        MsgBox "Sorry. Only Steve Ballmer can run this."

    End If

End Sub

You would probably agree that this version is much easier to follow.

A workbook that contains this section’s examples can be downloaded from this book’s website.

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 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, you can 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 statement. 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 preceding 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, using three If-Then 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

I added a new twist by using a variable. The Msg variable gets a different text value, depending on the time of day. The 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 slightly 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 evaluating 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 critical, you should know about another syntax for the If-Then structure.

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 appropriate 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 routine 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 examples in this section are available at this book’s website.

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 checks 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 structures. This routine examines the active cell and displays a message describing the cell’s contents. Notice that the procedure has three Select Case structures, 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 Else

                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.

Figure 10-1: A message displayed by the CheckCell procedure.

If you’re still not convinced that indenting code is worth the effort, the previous listing 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 Else

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

Fairly incomprehensible, eh?

Knocking Your Code for a Loop

The term looping refers to repeating a block of VBA statements numerous times. Why use loops? I can think of a few reasons. Your code can . . .

Loop through a range of cells, working with each cell individually.

Loop through all open workbooks (the Workbooks collection) and do something with each one.

Loop through all worksheets in a workbook (the Worksheets collection) and do something with each one.

Loop through all the elements in an array.

Loop through all characters in a cell.

Loop through all charts on a worksheet (the ChartObjects collection) and do something with each chart.

Loop through other things that I haven’t thought of.

VBA supports several types of loops, and the examples that follow demonstrate a few ways that you can use them.

For-Next loops

The examples in this section are all available at this book’s website.

The simplest type of loop is a For-Next loop. The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how this works, keep reading.

A For-Next example

The following example uses a For-Next loop to sum the first 1,000 positive integers. The Total variable starts out as zero. Then the looping occurs. The variable Cnt is the loop counter. It starts out as 1 and is incremented by 1 each time through the loop. The loop ends when Cnt is 1,000.

This example has only one statement inside of the loop. This statement adds the value of Cnt to the Total variable. When the loop finishes, a MsgBox displays the summed integers.

Sub AddNumbers()

    Dim Total As Double

    Dim Cnt As Integer

    Total = 0

    For Cnt = 1 To 1000

        Total = Total + Cnt

    Next Cnt

    MsgBox Total

End Sub

Because the loop counter is a normal variable, you can write code to change its value within the block of code between the For and the Next statements. This, however, is a very bad practice. Changing the counter within the loop can have unpredictable results. Take special precautions to ensure that your code does not change the value of the loop counter.

For-Next examples with a Step

You can use a Step value to skip some counter values in a For-Next loop. Here’s the previous example, rewritten to sum only the odd integers between 1 and 1,000:

Sub AddOddNumbers()

    Dim Total As Double

    Dim Cnt As Integer

    Total = 0

    For Cnt = 1 To 1000 Step 2

        Total = Total + Cnt

    Next Cnt

    MsgBox Total

End Sub

This time, Cnt starts out as 1 and then takes on values of 3, 5, 7, and so on. The Step value determines how the counter is incremented. Notice that the upper loop value (1000) is not actually used because the highest value of Cnt will be 999.

Here’s another example that uses a Step value of 3. This procedure works with the active sheet, and applies light gray shading to every third row, from row 1 to row 100.

Sub ShadeEveryThirdRow()

    Dim i As Long

    For i = 1 To 100 Step 3

        Rows(i).Interior.Color = RGB(200, 200, 200)

    Next i

End Sub

Figure 10-2 shows the result of running this macro.

Figure 10-2: Using a loop to apply background shading to rows.

A For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately.

The following example, available on the book’s website, demonstrates the Exit For statement. This routine is a Function procedure, intended to be used in a worksheet formula. The function accepts one argument (a variable named Str) and returns the characters to the left of the first numeric digit. For example, if the argument is “KBR98Z,” the function returns “KBR.”

Function TextPart(Str)

    TextPart = ""

    For i = 1 To Len(Str)

        If IsNumeric(Mid(Str, i, 1)) Then

            Exit For

        Else

            TextPart = TextPart & Mid(Str, i, 1)

        End If

    Next i

End Function

The For-Next loop starts with 1 and ends with the number that represents the number of characters in the string. The code uses VBA’s Mid function to extract a single character within the loop. If a numeric character is found, the Exit For statement is executed, and the loop ends prematurely. If the character is not numeric, it is appended to the returned value (which is the same as the function’s name). The only time the loop will examine every character is if the string passed as the argument contains no numeric characters.

---

---

Now you might shout, “Hey, but you said something about always using a single point of exit!” Well, you’re right, and obviously you’re getting the hang of this structured programming business. But in some cases, ignoring that rule is a wise decision. In this example, it will greatly speed up your code because there’s no reason to continue the loop after the first numeric digit is found.

A nested For-Next example

So far, all this chapter’s examples use relatively simple loops. However, you can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.

The following example uses a nested For-Next loop to insert random numbers into a 12-row-x-5-column range of cells, as shown in Figure 10-3. Notice that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) = Rnd statement 60 times.

Sub FillRange()

    Dim Col As Long

    Dim Row As Long

    For Col = 1 To 5

        For Row = 1 To 12

            Cells(Row, Col) = Rnd

        Next Row

    Next Col

End Sub

Figure 10-3: These cells were filled by using a nested For-Next loop.

The next example uses nested For-Next loops to initialize a three-dimensional array with the value 100. This routine executes the statement in the middle of all the loops (the assignment statement) 1,000 times (10 * 10 * 10), each time with a different combination of values for i, j, and k:

Sub NestedLoops()

    Dim MyArray(10, 10, 10)

    Dim i As Integer

    Dim j As Integer

    Dim k As Integer

    For i = 1 To 10

        For j = 1 To 10

            For k = 1 To 10

                MyArray(i, j, k) = 100

            Next k

        Next j

    Next i

    ' Other statements go here

End Sub

Refer to Chapter 7 for information about arrays.

Here’s a final example that uses nested For-Next loops, with a Step value. This procedure creates a checkerboard (or chessboard, if you prefer) by changing the background color of alternating cells. See Figure 10-4.

Figure 10-4: Using loops to create a checker-board pattern.

The Row counter loops from 1 to 8. An If-Then construct determines which nested For-Next structure to use. For odd-numbered rows, the Col counter begins with 2. For even-numbered rows, the Col counter begins with 1. Both loops use a Step value of 2, so alternate cells are affected.

Sub MakeCheckerboard()

    Dim Row As Integer, Col As Integer

    For Row = 1 To 8

        If WorksheetFunction.IsOdd(Row) Then

            For Col = 2 To 8 Step 2

                Cells(Row, Col).Interior.Color = 255

            Next Col

        Else

            For Col = 1 To 8 Step 2

                Cells(Row, Col).Interior.Color = 255

            Next Col

        End If

    Next Row

End Sub

Do-While loop

VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified condition is met.

The following example uses a Do-While loop. This routine uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 2. The loop continues until the routine encounters an empty cell.

Sub DoWhileDemo()

    Do While ActiveCell.Value <> Empty

        ActiveCell.Value = ActiveCell.Value * 2

        ActiveCell.Offset(1, 0).Select

    Loop

End Sub

Do-Until loop

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true.

The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop:

Sub DoUntilDemo()

    Do Until IsEmpty(ActiveCell.Value)

        ActiveCell.Value = ActiveCell.Value * 2

        ActiveCell.Offset(1, 0).Select

    Loop

End Sub

Using For Each-Next Loops With Collections

VBA supports yet another type of looping — looping through each object in a collection of objects. Recall that a collection consists of a number of objects of the same type. For example, Excel has a collection of all open workbooks (the Workbooks collection), and each workbook has a collection of worksheets (the Worksheets collection).

The examples in this section are all available at this book’s website.

When you need to loop through each object in a collection, use the For Each-Next structure. The following example loops through each worksheet in the active workbook and deletes the worksheet if it’s empty:

Sub DeleteEmptySheets()

    Dim WkSht As Worksheet

    Application.DisplayAlerts = False

    For Each WkSht In ActiveWorkbook.Worksheets

        If WorksheetFunction.CountA(WkSht.Cells) = 0 Then

            WkSht.Delete

        End If

    Next WkSht

    Application.DisplayAlerts = True

End Sub

In this example, the variable WkSht is an object variable that represents each worksheet in the workbook. Nothing is special about the variable name WkSht — you can use any variable name that you like.

The code loops through each worksheet and determines an empty sheet by counting the nonblank cells. If that count is zero, the sheet is empty, and it’s deleted. Notice that I turn off the DisplayAlerts setting while the loop is doing its thing. Without that statement, Excel pops up a warning every time a sheet is about to be deleted.

Here’s another For Each-Next example. This procedure uses a loop to hide all worksheets in the active workbook, except the active sheet.

Sub HideSheets()

    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Worksheets

        If Sht.Name <> ActiveSheet.Name Then

            Sht.Visible = xlSheetHidden

        End If

    Next Sht

End Sub

The HideSheets procedure checks the sheet name. If it’s not the same as the active sheet’s name, then the sheet is hidden. Notice that the Visible property isn’t Boolean. This property can actually take on any of three values, and Excel provides three built-in constants. If you’re curious about the third possibility, check the Help system.

What gets hidden must eventually get unhidden, so here’s a macro that unhides all worksheets in the active workbook:

Sub UnhideSheets()

    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Worksheets

        Sht.Visible = xlSheetVisible

    Next Sht

End Sub

Not surprisingly, you can create nested For Each-Next loops. The CountBold procedure loops through every cell in the used range on each worksheet in every open workbook and displays a count of the number of cells that are formatted as bold:

Sub CountBold()

    Dim WBook As Workbook

    Dim WSheet As Worksheet

    Dim Cell As Range

    Dim cnt As Long

    For Each WBook In Workbooks

        For Each WSheet In WBook.Worksheets

            For Each Cell In WSheet.UsedRange

               If Cell.Font.Bold = True Then cnt = cnt + 1

            Next Cell

        Next WSheet

    Next WBook

    MsgBox cnt & " bold cells found"

End Sub