VBA Basics to Read – Right after the Hello World Program
After the Hello World program, there was no topics posted for beginners in OfficeTricks. So here is one that would benefit the learners. 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.
Note: A new category “Excel Macro Beginners” has been created, that includes all the VBA basic topics in this website.
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.
Visual Basic123456789101112131415161718192021222324252627282930313233343536Sub Testing_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'''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.
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”).
Visual Basic123456789101112131415161718192021222324Sub Testing_Conditional_Statements()'''Variable DeclarationDim i As Integeri = 2'''Conditional StatementIf i = 1 ThenMsgBox "If: #" & iElseMsgBox "If.Else: #" & iEnd Ifi = i + 1'''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
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 Macro Looping Structure and Conditional Statements