Most Useful Excel VBA Control Structures

Control Structures are most powerful feature of any programming language. Similarly, there are some Control Structures which is commonly used by VBA programming. Usually, these control structures are very useful to save ample amount of time to write large number of line of codes. It helps to facilitate user to work on very complex logic and calculations.

Following is a list of the most commonly used Excel VBA programming control structures. Detailed illustration with few simple example and brief description is given below. Which will be very helpful to know when you would use that structure into your project.


Refer below sample examples and illustration:

1) For – Next
Useful for looping through an array

ArraySum = 0
For i = 1 To 10
    ArraySum = ArraySum + MyArray(i)
Next i

2) 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

3) 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

4) 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

5) If – Then
Do something if a condition is true. Written as a single statement

If x = 1 Then y = 1

6) 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

7) 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

8) 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

9) 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

10) Goto
Jump to a labeled statement. This is used mostly for error handling

On Error GoTo Oops
'[your code goes here]
Exit Sub
Oops:
MsgBox "An error occurred"