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"