How to Control Program Flow in Excel Visual Basic
In Alice in Wonderland by Lewis Carroll, at the trial of the Knave of Hearts, the White Rabbit asks the King how to read the evidence. “Begin at the beginning, the King said, very gravely, and go on till you come to the end: then stop.” It would be easy if all computer programs did this! Real life is more complicated. There are decisions to make! Some code must execute many times or, sometimes, not at all. Thus, we come to program flow control with branching and looping commands.
1. Spaghetti Programming vs. Structured Programming
We often want a program to take an action depending on the result of some comparison or condition. For example, we want to avoid dividing by zero.
We need a way of continuing execution of the program at a different point.
Those who have programmed in early dialects of Basic will probably have encountered…
1.1 The Dreaded “GoTo”
In our previous post, we used the following error-trapping:
Sub Invert1() ' Invert Numeric Text, with primitive Error trap:
Dim Text1$
Text1$ = InputBox("Input a Number to Invert")
If Val(Text1$) = 0 Then MsgBox "Can't divide by Zero!": Exit Sub
MsgBox Text1$ & " Inverted = " & (1 / Val(Text1$))
End Sub
This is messy. We have to put all the statements that depend on the “If”, on one line. This can produce very long lines if we have detailed messages.
Using the GoTo statement with line labels, we can neaten this up as follows.
Sub Invert2() ' Invert Numeric Text, with better Error trap:
Dim Text1$
Text1$ = InputBox("Input a Number to Invert")
If Val(Text1) = 0 Then GoTo DivZero ' Error-trapping
MsgBox Text1$ & " Inverted = " & (1 / Val(Text1$))
GoTo MyEnd
DivZero: ' Error occurred:
Beep
MsgBox "Can't divide by zero! Please try something else."
MyEnd: ' Normal End:
End Sub
Line labels end in a colon (“:”).
While this code is longer, it makes more sense. The error trap is in its own section.
There is, however, a severe danger in using GoTo. If you use it more than once or twice, your code becomes very difficult to follow and debug. This is what we mean by the term “Spaghetti Programming”. Much as we love eating spaghetti, programming it is a bad idea.
1.2 Structured Programming
This is the alternative to Spaghetti Programming: Its principle is “One Entry, One Exit”.
We will not use GoTo at all. Instead, we will use the following program control structures:
2. Branching Structures
2.1 If … Then … Else
This is the multi-line version of the simple one-line If … Then statement we used in the first example (Sub Invert1) above. It looks like this:
If (condition) Then
(do something)
[Else
(do something else)]
End If
Note: The “Else” block is optional, and is thus shown in [square] brackets in the syntax convention. The square brackets are not used in the actual code.
Rewriting our zero-trapping using this structure, we get:
Sub Invert3() ‘ Invert Numeric Text, with better Error trap:
Sub Invert3() ' Invert Numeric Text, with better Error trap:
Dim Text1$, Answer#
Text1$ = InputBox("Input a Number to Invert")
If Val(Text1) = 0 Then ' Error-trapping:
Beep
MsgBox "Can't divide by zero! Please try something else."
Else ' Calculate answer:
Answer = 1 / Val(Text1)
MsgBox Text1$ & " Inverted = " & Answer
End If
End Sub
Notice the indention used to show clearly what code is inside the If / Else blocks. Use Tab to indent, Shift+Tab to reduce indention. They act on all the lines you have selected.
To select (highlight) text, drag from the start to the end of what you want to select. To select whole lines, drag up or down in the white area to the left of the text. The mouse pointer changes from an I-beam to a “North-East” pointing arrow.
2.2 ElseIf
An extension of the If … Then … Else structure is:
If (condition) Then
(do something)
[ElseIf (another condition) Then
(do something different)]
[Else
(do something else)]
End If
You can use as many ElseIf statements as you like between the initial If and the final Else. The latter is still optional.
2.3 Select Case Statement
This executes one of several groups of statements, depending on the value of an expression:
Select Case expression
[Case expressionlist1
[statements]] . . .
[Case expressionlist2
[statements]] . . .
[Case expressionlistn
[statements]] . . .
[Case Else
[statements]] . . .
End Select
Expressionlist is a list in one or more of the following forms:
- expression, expression, expression (e.g.. 6, 7, 8)
- expression to expression (an inclusive range of values, from small to big)
- Is comparison-operator expression (e.g. Is >= 6)
3. Looping Structures
Sometimes we want to repeat a certain block of code a given number of times. Or it must repeat while a certain condition is true, or until a condition occurs.
3.1 For … Next Loops
Syntax:
For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]
The optional [Exit For] lets you exit the loop before it reaches the end value. It would usually be in an If statement.
The counter in the Next statement is optional, but it is good programming practice to include it. Notice the indention.
Example:
Dim I&, J&
For I& = 1 To 10
J& = J& * I&
If J& > 100 Then Exit For
Next I&
3.2 For Each … Next Loops
This newer structure in Visual Basic repeats a group of statements for each element in an array or collection. You cannot rely on the elements being processed in any particular order.
Syntax:
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
Example (Worksheets is a Collection defined by Excel):
Sub ForEach() ' Example of "For Each" Loop
' Display Names of all Worksheets:
Dim wrx As Worksheet
For Each wrx In Worksheets
MsgBox wrx.Name
Next wrx
End Sub
3.3 While … Wend Loops
This loop executes a series of statements as long as a given condition is True. If the condition is False to start off with, the loop does not execute at all.
Syntax:
While condition
[statements]
Wend
In the new VB.NET, Wend has been replaced by End While. It also has Exit While to drop out of the loop, and Continue While to skip the rest of that iteration.
3.4 Do … Loop Loops
This loop repeats a block of statements while a condition is True or until a condition becomes True. There are two forms of syntax:
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop
Alternatively, you can put the condition at the end:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]
As with other Exit statements, the optional Exit Do allows the Loop to be exited prematurely. It is usually preceded by an If … Then statement.
In the case of the first syntax, the loop will not execute if the condition is False.
In the case of the second, it will execute at least once even if the condition is false.
4. Example
You can get the file Macros.xlsm free of charge from us. If you have, Click the button Fill Next 1000 Cells Below on Sheet1. Then press Alt+F11 for the VB Editor and look in the Module Code_Examples for the Sub Fill1000.
Otherwise, copy and paste the code below into a blank worksheet and then run it. Be aware that it will write over the current cell and cells below it.
This example demonstrates a For…Next loop and a While…Wend loop. The first loop walks down the spreadsheet, inserting the numbers 1 To 1000 in the cells. The code then moves two cells to the right. The second loop walks back up to the starting row, inserting the text “A1000” to “A1” into the cells.
Sub Fill1000() ' Fill values 1..1000 downwards
' Example of: Macro, For..Next loop, Putting value into cell
' Moving cell selector to new cell
Dim I&
For I& = 1 To 1000
ActiveCell.Formula = I& ' Put value into Current Cell
ActiveCell.Offset(1, 0).Select ' Move Down
Next I&
ActiveCell.Offset(0, 2).Select ' Move 2 Right
I& = 1000
While I& > 0 ' Repeat While condition is True
ActiveCell.Offset(-1, 0).Select ' Move Up
ActiveCell.Formula = "A " & I& ' Put text into Current Cell
I& = I& - 1
Wend
End Sub