Excel VBA Macro Tutorial: Looping

What is looping? In simple term, looping is a process of repeating tasks. There are three types of loops, For-Next, Do-Loop, and While-Wend. Which we will use depends on the objectives and conditions.


For-Next loops

Repeats a group of statements a specified number of times.

Syntax:

For counter = start To end [Step step]
  [statements]
  [Exit  For]
  [statements]
Next [counter]

The following example, we will puts number words "Excel VBA Macro" to range A1:A10 in active sheet.

Sub putWords()
  For myNum = 1 To 10
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Next myNum
End Sub

Do-Loop loops

Repeats a block of statements while a condition is True or until a condition becomes True.

Syntax:

Do [{While | Until}  condition]
  [statements]
  [Exit  Do]
  [statements]
Loop

Or, you can use this syntax:

Do
  [statements]
  [Exit  Do]
  [statements]
Loop [{While | Until} condition]

The output of this examples exactly same as example above.

Sub putWords2()
  myNum = 0
  Do
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Loop While myNum <>
End Sub  

Sub putWords3()   
   myNum = 0
   Do Until myNum = 10
      myNum = myNum + 1
      ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"   
   Loop
End Sub

While-Wend loops

Executes a series of statements as long as a given condition is True.

Syntax:

While condition
  [statements]
Wend

Example:

Sub putWords4()
  myNum = 0
  While myNum < 10
     myNum = myNum + 1
     ActiveSheet.Cells(myNum, 1).Value = "Excel VBA Macro"
  Wend
End Sub