Following is a list of the ten most commonly used Excel VBA programming control structures. Included is a simple example of each control structure and a brief description of when you would use that structure.
For – Next
Useful for looping through an array.
ArraySum = 0
For i = 1 To 10
ArraySum = ArraySum + MyArray(i)
Next i
For Each – Next
Useful for looping through a collection or a range of cells.
SelectionSum = 0
For Each cell In Selection
SelectionSum = SelectionSum + cell.Value
Next cell
Do – Loop Until
Loop until a condition is met.
Row = 0
Do
Row = Row + 1
Loop Until Cells(Row, 1).Value = "
MsgBox "First empty cell is in Row " & Row
Do While – Loop
Loop while a condition is true.
Row = 1
Do While Cells(Row, 1) <> "
Row = Row + 1
Loop
MsgBox "First empty cell is in Row " & Row
If – Then
Do something if a condition is true. Written as a single statement.
If x = 1 Then y = 1
If – Then – End If
Do something if a condition is true. Can use multiple statements.
If x = 1 Then
y = 1
z = 1
End If
If – Then – Else
Do something if a condition is true; otherwise, do something else. Written as a single statement.
If x = 1 Then y = 1 Else y = 0
If – Then – Else – End If
Do something if a condition is true; otherwise, do something else. Can use multiple statements.
If x = 1 Then
y = 1
Z = 1
Else
y = 0
Z = 0
End If
Select Case
Do one of several things, depending on a condition.
Select Case x
Case 1
y = 1
Z = 1
Case Is > 1
y = 2
Z = 2
Case Else
y = 0
Z = 0
End Select
Goto
Jump to a labeled statement. This is used mostly for error handling.
On Error GoTo Oops '[more code goes here] Exit Sub Oops: MsgBox "An error occurred"
