VBA Macro Excel Tutorial: Controlling Execution

This section introduces a few of the more common programming constructs that are used to control the flow of execution: If-Then construct, Select Case construct, and For-Next loops.

The If-Then construct
One of the most important control structures in VBA Macro Excel is the If-Then construct. The basic syntax of the If-Then structure is as follows:

If condition Then true_statements [Else false_statements]

Following is the example:

Sub Hello()
   inputName = InputBox("Enter your name: ")
   If inputName = "" then
     MsgBox "Hello, Anonymous."
   Else
     MsgBox "Hello, " & inputName & "."
End Sub


The Select Case construct
The Select Case construct is useful for choosing among two or more options. The syntax for Select Case is as follows:

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

Following example is an alternative to If-Then-Else:

Sub Hello()
   inputName = InputBox("Enter your name: ")
     Select Case inputName = ""
       MsgBox "Hello, Anonymous."
     Case Else
       MsgBox "Hello, " & inputName & "."
End Sub

This is another example of Select Case with three options:

Sub TheNumber()
   inputName = InputBox("Enter number (0-9): ")
     Select Case 0
       MsgBox "Zero Number"
     Case 1, 3, 5, 7, 9
       MsgBox "Odd Number"
     Case 2, 4, 6, 8
       MsgBox "Even Number"
     Case Else Exit Sub
End Sub


For-Next loops
You can use a For-Next loop to process a series of items. Following is an example of a For-Next loop:

Sub SumNumber()
   Total = 0
   For Num = 1 To 10
     Total = Total + (Num)
   Next Num
   MsgBox Total
End Sub