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
- To begin with we declare a variable ‘i’.
- 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.
- 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.
- 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.
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