Controlling Execution | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Some VBA procedures start at the top and progress line by line to the bottom. Often, however, you need to control the flow of your routines by skipping over some statements, executing some statements multiple times, and testing conditions to determine what the routine does next.

This section discusses several ways of controlling the execution of your VBA procedures:

If-Then constructs
Select Case constructs
For-Next loops
Do While loops
Do Until loops
On Error statements

The If-Then Construct

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This instruction is one way to endow your applications with decision-making capability. The basic syntax of the If-Then construct is as follows:

 If condition Then true_instructions [Else false_instructions]

The If-Then construct executes one or more statements conditionally. The Else clause is optional. If included, it enables you to execute one or more instructions when the condition that you test is not true.

The following Function procedure demonstrates an If-Then structure without an _Else clause. The example deals with time. VBA uses the same date-and-time serial number system as Excel. The time of day is expressed as a fractional value-for example, noon is represented as .5. The VBA Time function returns a value that represents the time of day, as reported by the system clock. In the following example, the function starts out by assigning an empty string to GreetMe. The If-Then statement checks the time of day. If the time is before noon, the Then part of the statement executes, and the function returns Good Morning.

 Function GreetMe()     GreetMe = ""     If Time < 0.5 Then GreetMe= "Good Morning" End Function

The following function uses two If-Then statements. It displays either Good Morning or Good Afternoon:

 Function GreetMe()     If Time < 0.5 Then GreetMe = "Good Morning"     If Time >= 0.5 Then GreetMe = "Good Afternoon" End Function

Notice that the second If-Then statement uses >= (greater than or equal to). This covers the extremely remote chance that the time is precisely 12:00 noon when the function is executed.

Another approach is to use the Else clause of the If-Then construct:

 Function GreetMe()     If Time < 0.5 Then GreetMe = "Good Morning" Else _       GreetMe = "Good Afternoon" End Function

Notice that the preceding example uses the line continuation sequence (a space followed by an underscore); If-Then-Else is actually a single statement.

The following is another example that uses the If-Then construct. This Function procedure calculates a discount based on a quantity (assumed to be an integer value). It accepts one argument (quantity) and returns the appropriate discount based on that value.

 Function Discount(quantity)     If quantity <= 5 Then Discount = 0     If quantity >= 6 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 End Function

Notice that each If-Then statement in this procedure is always executed, and the value for Discount can change as the function is executed. The final value, however, is the desired value.

The preceding examples all used a single statement for the Then clause of the If-Then construct. However, you often need to execute multiple statements if a condition is TRUE. You can still use the If-Then construct, but you need to use an End If statement to signal the end of the statements that make up the Then clause. Here's an example that executes two statements if the If clause is TRUE:

 If x > 0 Then     y = 2     z = 3 End If

You can also use multiple statements for an If-Then-Else construct. Here's an example that executes two statements if the If clause is TRUE, and two other statements if the If clause is not TRUE:

 If x > 0 Then     y = 2     z = 3 Else     y = -2     z = -3 End If

The Select Case Construct

The Select Case construct is useful for choosing among three or more options. This construct also works with two options and is a good alternative to using If-Then-Else. The syntax for Select Case is as follows:

 Select Case testexpression     [Case expressionlist-n         [instructions-n]]     [Case Else         [default_instructions]] End Select

The following example of a Select Case construct shows another way to code the GreetMe examples presented in the preceding section:

 Function GreetMe()     Select Case Time         Case Is < 0.5             GreetMe = "Good Morning"         Case 0.5 To 0.75             GreetMe = "Good Afternoon"         Case Else              GreetMe = "Good Evening"     End Select End Function

And here's a rewritten version of the Discount function from the previous section, this time using a Select Case construct:

 Function Discount2(quantity)     Select Case quantity         Case Is <= 5             Discount2 = 0         Case 6 To 24             Discount2 = 0.1         Case 25 To 49             Discount2 = 0.15         Case 50 To 74             Discount2 = 0.2         Case Is >= 75            Discount2 = 0.25     End Select End Function

Any number of instructions can be written below each Case statement; they all execute if that case evaluates to TRUE.

Looping Blocks of Instructions

Looping is the process of repeating a block of VBA instructions within a procedure. You may know the number of times to loop, or it may be determined by the values of variables in your program. VBA offers a number of looping constructs:

For-Next loops
Do While loops
Do Until loops

FOR-NEXT LOOPS

The following is the syntax for a For-Next loop:

 For counter = start To end [Step stepval]     [instructions]     [Exit For]     [instructions] Next [counter]

The following listing is an example of a For-Next loop that does not use the optional Step value or the optional Exit For statement. This function accepts two arguments and returns the sum of all integers between (and including) the arguments:

 Function SumIntegers(first, last)     total = 0     For num = first To last         total = total + num     Next num     SumIntegers = total End Function

The following formula, for example, returns 55-the sum of all integers from 1 to 10:

 =SumIntegers(1,10)

In this example, num (the loop counter variable) starts out with the same value as the first variable, and increases by 1 each time the loop repeats. The loop ends when num is equal to the last variable. The total variable simply accumulates the various values of num as it changes during the looping.

Caution

When you use For-Next loops, you should understand that the loop counter is a normal variable-it is not a special type of variable. As a result, you can change the value of the loop counter within the block of code executed between the For and Next statements. This is, however, a very bad practice and can cause problems. In fact, you should take special precautions to ensure that your code does not change the loop counter.

You also can use a Step value to skip some values in the loop. Here's the same function rewritten to sum every other integer between the first and last arguments:

 Function SumIntegers2(first, last)     total = 0     For num = first To last Step 2         total = total + num     Next num     SumIntegers2 = Total End Function

The following formula returns 25, which is the sum of 1, 3, 5, 7, and 9:

 =SumIntegers2(1,10)

For-Next loops can also include one or more Exit For statements within the loop. When this statement is encountered, the loop terminates immediately, as the following example demonstrates.

 Function RowOfLargest(c)     NumRows = Rows.Count     MaxVal = WorksheetFunction.Max(Columns(c))     For r = 1 To NumRows         If Cells(r, c) = MaxVal Then             RowOfLargest = r             Exit For         End If     Next r End Function

The RowOfLargest function accepts a column number (1–16,384) for its argument and returns the row number of the largest value in that column. It starts by getting a count of the number of rows in the worksheet. (This varies, depending on the version of Excel.) This number is assigned to the NumRows variable. The maximum value in the column is calculated by using the Excel MAX function, and this value is assigned to the MaxVal variable.

The For-Next loop checks each cell in the column. When the cell equal to MaxVal is found, the row number (variable r, the loop counter) is assigned to the function's name, and the Exit For statement ends the procedure. Without the Exit For statement, the loop continues to check all cells in the column-which can take quite a long time!

The previous examples use relatively simple loops. But you can have any number of statements in the loop, and you can even nest For-Next loops inside other For-Next loops. The following is VBA code that uses nested For-Next loops to initialize a 10 x 10 x 10 array with the value –1. When the three loops finish executing, each of the 1,000 elements in MyArray contains –1.

 Dim MyArray(1 to 10, 1 to 10, 1 to 10) For i = 1 To 10     For j = 1 To 10         For k = 1 To 10             MyArray(i, j, k) = -1         Next k     Next j Next i

DO WHILE LOOPS

A Do While loop is another type of looping structure available in VBA. Unlike a For-Next loop, a Do While loop executes while a specified condition is met. A Do While loop can have one of two syntaxes:

 Do [While condition]     [instructions]     [Exit Do]     [instructions] Loop

 Do    [instructions]    [Exit Do]    [instructions] Loop [While condition]

As you can see, VBA enables you to put the While condition at the beginning or the end of the loop. The difference between these two syntaxes involves the point in time when the condition is evaluated. In the first syntax, the contents of the loop may never be executed: That is, if the condition is met as soon as the Do statement is executed. In the second syntax, the contents of the loop are always executed at least one time.

The following example is the RowOfLargest function presented in the previous section, rewritten to use a Do While loop (using the first syntax):

 Function RowOfLargest2(c)     NumRows = Rows.Count     MaxVal = Application.Max(Columns(c))     r = 1     Do While Cells(r, c) <> MaxVal         r = r + 1     Loop     RowOfLargest2 = r End Function

The variable r starts out with a value of 1 and increments within the Do While loop. The looping continues as long as the cell being evaluated is not equal to MaxVal. When the cell is equal to MaxVal, the loop ends, and the function is assigned the value of r. Notice that if the maximum value is in row 1, the looping does not occur.

The following procedure uses the second Do While loop syntax. The loop always executes at least once.

 Function RowOfLargest(c)     MaxVal = Application.Max(Columns(c))     r = 0     Do         r = r + 1     Loop While Cells(r, c) <> MaxVal     RowOfLargest = r End Function

Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately.

DO UNTIL LOOPS

The Do Until loop structure closely resembles the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is true. In a Do Until loop, the loop executes until the condition is true. Do Until also has two syntaxes:

 Do [Until condition]     [instructions]     [Exit Do]     [instructions] Loop

 Do    [instructions]    [Exit Do]    [instructions] Loop [Until condition]

The following example demonstrates the first syntax of the Do Until loop. This example makes the code a bit clearer because it avoids the negative comparison required in the Do While example.

 Function RowOfLargest4(c)     NumRows = Rows.Count     MaxVal = Application.Max(Columns(c))     r = 1     Do Until Cells(r, c) = MaxVal         r = r + 1     Loop     RowOfLargest4 = r End Function

Finally, the following function is the same procedure but is rewritten to use the second syntax of the Do Until loop:

 Function RowOfLargest5(c)     NumRows = Rows.Count     MaxVal = Application.Max(Columns(c))     r = 0     Do         r = r + 1     Loop Until Cells(r, c) = MaxVal     RowOfLargest5 = r End Function

The On Error Statement

Undoubtedly, you've used one of Excel's worksheet functions in a formula and discovered that the formula returns an error value (for example, #VALUE!). A formula can return an error value in a number of situations, including these:

You omitted one or more required argument(s).
An argument was not the correct data type (for example, text instead of a value).
An argument is outside of a valid numeric range (division by zero, for example).

In many cases, you can ignore error handling within your functions. If the user does not provide the proper number of arguments, the function simply returns an error value. It's up to the user to figure out the problem. In fact, this is how Excel's worksheet functions handle errors.

In other cases, you want your code to know if errors occurred and then do something about them. Excel's On Error statement enables you to identify and handle errors.

To simply ignore an error, use the following statement:

 On Error Resume Next

If you use this statement, you can determine whether an error occurs by checking the Number property of the Err object. If this property is equal to zero, an error did not occur. If Err.Number is equal to anything else, an error did occur.

The following example is a function that returns the name of a cell or range. If the cell or range does not have a name, an error occurs, and the formula that uses the function returns a #VALUE! error.

 Function RANGENAME(rng)    RANGENAME = rng.Name.Name End Function

The following list shows an improved version of the function. The On Error Resume Next statement causes VBA to ignore the error. The If Err statement checks whether an error occurs. If so, the function returns an empty string.

 Function RANGENAME(rng)     On Error Resume Next     RANGENAME = rng.Name.Name     If Err.Number <> 0 Then RANGENAME = "" End Function

The following statement instructs VBA to watch for errors; and if an error occurs, continues executing at a different named location-in this case, a statement labeled ErrHandler.

 On Error GoTo ErrHandler

The following Function procedure demonstrates this statement. The DIVIDETWO function accepts two arguments (num1 and num2) and returns the result of num1 divided by num2.

 Function DIVIDETWO(num1, num2)     On Error GoTo ErrHandler     DIVIDETWO = num1 / num2     Exit Function ErrHandler:     DIVIDETWO = "ERROR" End Function

The On Error GoTo statement instructs VBA to jump to the statement labeled ErrHandler if an error occurs. As a result, the function returns a string (ERROR) if any type of error occurs while the function is executing. Note the use of the Exit Function statement. Without this statement, the code continues executing, and the error handling code always executes. In other words, the function always returns ERROR.

It's important to understand that the DIVIDETWO function is non-standard in its approach. Returning an error message string when an error occurs (ERROR) is not how Excel functions work. Excel functions return an actual error value.

Cross Ref

Chapter 25 contains several examples of the On Error statement, including an example that demonstrates how to return an actual error value from a function.