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>

  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

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