Home

Top Ten Excel VBA Control Structures

Updated
2016-03-26 15:49:52
From the book
No items found.
Share
Microsoft 365 Excel For Dummies
Microsoft 365 Excel For Dummies book cover
Explore Book
Subscribe on Perlego
Microsoft 365 Excel For Dummies
Microsoft 365 Excel For Dummies book coverExplore Book
Subscribe on Perlego

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"

About This Article

This article is from the book: 

No items found.

About the book author:

No items found.