Excel VBA Programming For Dummies Cheat Sheet

Top Ten Excel VBA Control Structures

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"
blog comments powered by Disqus
Advertisement

Inside Dummies.com