Excel VBA Tutorial on Loops

Use loops in your VBA code for more robust scripts.

VBA is Microsoft's Visual Basic programming language. It is most commonly associated with macros used in Excel spreadsheets and sometimes even report generation with output targeted at Word or PowerPoint documents. VBA offers developers all the common logic options available in other languages, such as for loops, while loops and if/then/else statements. However, like all languages, the syntax of using these loops is a bit different in VBA. Once you use these, the process is easy and will be simple to implement.

Instructions

1. Create an If statement in VBA such as this example that compares two values and writes "EQUAL" or "NOT EQUAL" into cell A1 depending on the result of the comparison.

   Sub My_If_Test()

   Dim this_value

   Dim that_value

   this_value = 0

   that_value = 2

   If this_value = that_value Then

   Sheets("Sheet1").Cells(1,1).Value = "EQUAL"

   Else

   Sheets("Sheet1").Cells(1,1).Value = " NOT EQUAL"

   End If

   End Sub

   If you need to test if something is not equal, the sign is <>.

2. Create a For loop in VBA following this example code that will insert the value of an incremented counter into each corresponding row.

   Sub My_For_Test()

   Dim counter

   Dim end_value

   end_value = 10

   For counter = 0 to end_value Step 1

   Sheets("Sheet1").Cells(counter,1).Value = counter

   Next

   End Sub

3. Create a Do/While loop in VBA following this example code that, like the For loop example, inserts the value of an incremented counter into each corresponding row.

   Sub My_DoWhile_Test()

   Dim index

   Dim end_value

   index = 0

   end_value = 10

   Do While index < end_value

   Sheets("Sheet1").Cells(counter,1).Value = index

   index = index + 1

   Loop

   End Sub

   The logic of the Do/While loop is such that, if the initial comparison of the While condition fails, the loop is not executed. For instance, if index had equaled 10 or more before the code executed this Do/While loop, the code inside the loop would never run.

4. Create a Do/Until loop in VBA following this example code that, like the Do/While loop example, inserts the value of an incremented counter into each corresponding row.

   Sub My_DoUntil_Test()

   Dim index

   Dim end_value

   index = 0

   end_value = 10

   Do

   Sheets("Sheet1").Cells(counter,1).Value = index

   index = index + 1

   Loop Until index = end_value

   End Sub

   The logic of the Do/Until loop is subtly different than a Do/While loop. The code in the Do/Until is executed at least one time even if the Until condition fails. For instance, if index had equaled 10 or more before the code executed this Do/Until loop, the code inside the loop would be executed one time anyway.

Resources

• ExcelTip.com: Using Loops in VBA in Microsoft Excel