Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!