Excel Vba For Each VBA If Else Switch Case

Excel VBA IF Else – For Each

After the Hello World program, there was no topics posted for beginners in OfficeTricks.

So here is one that would benefit the learners by learning bit of VBA If Else & For While looping structure. This topic is about different methods of looping structure (i.e., execute a set of code repeatedly till a condition is satisfied) and conditional check commands.

Excel VBA For Each

We are going to learn below listed 4 types of looping structures with an example, which uses these commands and display a message to the user inside each loop.

• For Next
• While Wend
• Do <While/Until> Loop
• Do Loop <While/Until>

Sub VBA_For_Do_While_Loops()
    'Variable Declaration
    Dim i As Integer

    'For <Variable> = Initial-Value-To-Begin-Loop To Last-Value-To-Stop-Looping ... Next <Variable>
    For i = 1 To 2
        MsgBox "For Loop: # " & i
        ThisWorkbook.Sheets(1).Cells(i, 1) = "For Loop: #" & i
        For j = 4 To 5
            MsgBox "For Loop: j#" & j
            ThisWorkbook.Sheets(1).Cells(i, j) = "For Loop: j#" & j
        Next j
    Next i

    'VBA For Each Loop
    Dim nums(1) As Integer, num As Variant
    nums(0) = 1
    nums(1) = 2
    
    For Each num In nums
        MsgBox num
    Next num

    'While (Conditional Statement) ... Wend
    While i < 4
        MsgBox "While Loop: #" & i
        ThisWorkbook.Sheets(1).Cells(i, 1) = "While Loop: #" & i
        i = i + 1
    Wend

    'Do Loop with Conditional Statement at Start of Loop
    Do Until i > 4
        MsgBox "Do Until Loop: #" & i
        ThisWorkbook.Sheets(1).Cells(i, 1) = "Do Until Loop: #" & i
        i = i + 1
    Loop

    'Do Loop with Conditional Statement at End of Loop
    Do
        MsgBox "Do Loop While: #" & i
        ThisWorkbook.Sheets(1).Cells(i, 1) = "Do Loop While: #" & i
        i = i + 1
    Loop While (i < 5)

End Sub

1. To begin with we declare a variable ‘i’.
2. For Loop will initialize the variable ‘i’ with 1 and will keep on incrementing it – 1 by 1 till it reaches the last value. Every Looping structure should be coding with a corresponding Scope Terminators (“Next”, “Wend”, “Loop”). If a scope terminator is coded at a incorrect place, the looping will execute unnecessary commands.
3. If we are not sure about initial value or ending value and we want to execute set of commands till a condition is satisfied, use While Wend or Do Loop.
4. In General the example will execute different type of Loops, displaying a message box when executed and will also insert the message Worksheet.

VBA IF Else – Switch Case

In our code if want to execute any code based on a condition, use IF END IF statement. In case we have set of Conditional checks to be performed use Select Case Statement. Similar to Looping structure, Conditional checks should also end with proper Scope Terminators (“End If”, ”End Select”).

• If Else End if
• Select Case End Select

Sub IF_Else_Switch_Case_Conditional_Statements()
    'Variable Declaration
    Dim i As Integer
    i = 2

    'VBA IF ELSE - Conditional Statement
    If i = 1 Then
        MsgBox "If: #" & i
    Else
        MsgBox "If.Else: #" & i
    End If
    i = i + 1

    'VBA Switch Case - Handling Mutiple Contional Statements
    Select Case i
    Case 1
        MsgBox "Switch.Case: # " & i
    Case 2
        MsgBox "Switch.Case: # " & i
    Case Else
        MsgBox "Switch.Case.Else: # " & i
    End Select

End Sub

Note: A new category “Excel Macro Beginners” has been created, that includes all the VBA basic topics in this website.

Additional Reading

Try reading about the below Keywords from MSDN which will also help in how to handle error conditions and how to skip a loop in the middle of execution in a excel macro code.

• Continue <For/While/Do> – Skip execution to next level of Iteration
• On Error <Resume/Goto> – What to do when any error occurs during code execution
• Goto <Label> – Transfer control to a Label

MSDN Link For Looping Structure: http://msdn.microsoft.com/en-us/library/801hyx6f.aspx