12. Control of Macro Flow - Decisions

12.1 Flow of Macro Code

The "flow" of a macro or program refers to the order in which the statements are executed. When you record a macro and then run it, each macro command is executed once, in order, beginning at the first command. However, you can alter the flow by having part of the macro execute or not, depending on a condition. If the condition is true, one set of commands is run, and if it is false those commands are not run; optionally if it is false a different set of commands is run. You can also have part of the macro executed repeatedly, either while a condition is true, or for a certain number of repetitions, in a loop.

12.2 Condition Expressions

A condition is an expression that is either true or false. If you have used the IF function in Excel formulas you are already familiar with conditions. The operators for the most common conditions, for equality/comparison, are as follows:

=  equal to
>  greater than
>= greater than or equal to
<  less than
>= less than or equal to
<> not equal to

Remember that text must be placed in quotes; text not in quotes is considered a variable.

Examples:

x = "Smith" true if the value of variable x is "Smith" (note that text comparison is case-sensitive)
x > 15 true if the value of variable x is greater than 15
ActiveCell.RowHeight > 15 true if the height of the row where the active cell is located is greater than 15
y < 20 true if the value of variable y is less than 20
x >= ActiveCell.Row true if the value of variable x is greater than or equal to the row number of the active cell
y <= x true if the value of variable y is less than or equal to the value of variable x
x <> 50 true if the value of variable x is anything other than 50
Len(x) <> 10 true if the length of the string in variable x is anything other than 10

You can use more than one expression in a condition with the operators And and Or. And means both of the expressions are true, e.g., x = 5 And y = 9 is true if x = 5 and y = 9. Or means either of the conditions is true, e.g., x = 5 Or y = 9 is true if either x = 5 or y = 9. For complex expressions, you can use parentheses to ensure that the expression is evaluated correctly, e.g., x = 5 And (y = 3 Or y = 9).

You can construct the negative of a condition with the Not operator; Not x = 5 is true if x is not equal to 5.

12.3 Conditional Execution - If Statements

When a set of macro commands is to be executed only when a condition is true, the If...Then...End If statement is used. If some commands are to be executed if a condition is true, and another set executed if the condition is false, then the If...Then...Else...End If statement is used. It is good programming practice to indent the commands within the If statement.

For example, to insert "THE END" after the last row of a worksheet and bold that cell, you could use the following code to test if the cell in the first column of the next row is blank:

vRow = ActiveCell.Row

If Cells(vRow + 1, 1).Value = "" Then
Cells(vRow + 1, 1).Value = "THE END"
Cells(vRow + 1), 1).Font.Bold = True
End If

To do something else if the active cell is not the last row in the spreadsheet:

vRow = ActiveCell.Row

If Cells(vRow + 1, 1).Value = "" Then
Cells(vRow + 1, 1).Value = "THE END"
Cells(vRow + 1), 1).Font.Bold = True
Else
do something else
End If
The condition being tested must evaluate to true or false. If for some reason the macro cannot evaluate the condition, you will get an error.

If there is just one command to execute, and no Else block, you can put the whole thing on one line, and omit the concluding End If.

If ActiveCell.Value < 100 Then ActiveCell.EntireRow.Delete

You can nest If statements inside one another; just be sure to include an End If statement to conclude each If statement. Indenting for each level will help you keep track.

12.4 Alternative Conditions - Select Case Statements

A simple If statement works fine when you just have two alternatives: do one thing if condition A is true, do something else (or nothing) if it is not true. However, often you will have more than two alternatives. For example, there might be several possible values for the cells in a column on a worksheet, and you might want to execute different code depending on the value. You can do that with the Select Case...End Select statement. The form of this statement is:
Select Case expression tested
Case possible value of the expression tested
macro code
Case possible value of the expression tested
macro code
Case possible value of the expression tested
macro code
Case Else
macro code
End Select
The Case Else is optional; its code will be executed if the expression tested does not match any of the other values.

As an example, let us say we want to check the value in the cell in column "C", and if it is 1, bold the cell in column "D"; if it is 2, italicize the cell in column "E"; and if it is "none", delete the text in column "F". We could do that with the following code:

x = Cells(2, "C").Value

Select Case x
Case 1
Cells(2, "D").Font.Bold = True
Case 2
Cells(2, "E").Font.Italic = True
Case "none"
Cells(2, "F").Value = ""
End Select

That code works fine for row 2. But we would probably want to have a more robust version that could process all the rows, rather than just one. We will learn how to do that later in this tutorial with loops.

12.5 Testing for Success/Failure of a Find

You will recall that a find that is not successful returns Nothing. You can test for this result by checking if a variable Is Nothing. or the reverse using Not [variable] Is Nothing.

For example, the following code checks if there is no cell that contains "hello"; if so then it sounds a beep, otherwise it activates the first cell that it finds that contains "hello".

Set x = Cells.Find(What:="hello", After:=Range("A1"), LookIn:=xlValues, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, _
                 SearchDirection:=xlNext, MatchCase:=False, _
                 SearchFormat:=False)

If x Is Nothing Then
     Beep
Else
     x.Activate
End If
We can code this slightly differently, by first checking if there there is any cell containing "hello"; if there is the macro activates the first cell it finds, otherwise it sounds a beep.
Set x = Cells.Find(What:="hello", After:=Range("A1"), LookIn:=xlValues, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, _
                 SearchDirection:=xlNext, MatchCase:=False, _
                 SearchFormat:=False)

If Not x Is Nothing Then
     x.Activate
Else
     Beep
End If