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.
Also Read: Excel VBA - Merge Cells - Unmerge Range
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.
VBA For Do While LoopVisual Basic123456789101112131415161718192021222324252627282930313233343536373839404142434445Sub VBA_For_Do_While_Loops()'Variable DeclarationDim i As Integer'For <Variable> = Initial-Value-To-Begin-Loop To Last-Value-To-Stop-Looping ... Next <Variable>For i = 1 To 2MsgBox "For Loop: # " & iThisWorkbook.Sheets(1).Cells(i, 1) = "For Loop: #" & iFor j = 4 To 5MsgBox "For Loop: j#" & jThisWorkbook.Sheets(1).Cells(i, j) = "For Loop: j#" & jNext jNext i'VBA For Each LoopDim nums(1) As Integer, num As Variantnums(0) = 1nums(1) = 2For Each num In numsMsgBox numNext num'While (Conditional Statement) ... WendWhile i < 4MsgBox "While Loop: #" & iThisWorkbook.Sheets(1).Cells(i, 1) = "While Loop: #" & ii = i + 1Wend'Do Loop with Conditional Statement at Start of LoopDo Until i > 4MsgBox "Do Until Loop: #" & iThisWorkbook.Sheets(1).Cells(i, 1) = "Do Until Loop: #" & ii = i + 1Loop'Do Loop with Conditional Statement at End of LoopDoMsgBox "Do Loop While: #" & iThisWorkbook.Sheets(1).Cells(i, 1) = "Do Loop While: #" & ii = i + 1Loop While (i < 5)End Sub
- For Next
- While Wend
- Do <While/Until> Loop
- Do Loop <While/Until>
- 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”).
VBA IF Else Switch CaseVisual Basic123456789101112131415161718192021222324Sub IF_Else_Switch_Case_Conditional_Statements()'Variable DeclarationDim i As Integeri = 2'VBA IF ELSE - Conditional StatementIf i = 1 ThenMsgBox "If: #" & iElseMsgBox "If.Else: #" & iEnd Ifi = i + 1'VBA Switch Case - Handling Mutiple Contional StatementsSelect Case iCase 1MsgBox "Switch.Case: # " & iCase 2MsgBox "Switch.Case: # " & iCase ElseMsgBox "Switch.Case.Else: # " & iEnd SelectEnd Sub
- If Else End if
- Select Case End Select
Note: A new category “Excel Macro Beginners” has been created, that includes all the VBA basic topics in this website.
Also Read: Excel VBA - Convert Column Letter to Number
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
Excel Vba For Each VBA If Else Switch Case