Different Looping Mechanisms in Excel VBA

looping logo
msofficeshare logo

File to download:  Looping.xlsm

Requirement:  Visual Basic knowledge

Related article: FIFO and LIFO CalculationsNavigate around cells, worksheets, and files by using Visual Basic Codes

There are several ways of using looping in Excel.  In general, there are two main loop mechanisms in Excel VBA: the FOR loop and the DO loop.  Each mechanism can be further structured in different ways to make it work as desired.  There is no inherent superiority of one loop mechanism over another.  The users are free to choose the looping mechanisms that are better for the users’ familiarity or for the tasks.

pic1 looping

The left illustration shows the calculation of the total of a list of variables from cell A4 to A16.  The total is placed at cell A18.  This is just an example of how to use looping in Excel VBA for simplistic purpose.  Of course the users can always use the Excel SUM function instead.

FOR Loop

 

The syntax is:

For n = <begin looping value> To <end looping value>  

               …….

Next n

Below is the entire code of using FOR loop.

Sub ForLoop()

    Dim n As Integer, SumTotal As Long

   

    n = 1

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    For n = 1 To 13

        SumTotal = SumTotal + ActiveCell.Value

        ActiveCell.Offset(1, 0).Select

    Next n

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

To use a pure FOR loop, you need to know how many variables in your calculation.  In our example, there are 13 variables to be summed up.  If you later add another variable, you need to modify the VB module accordingly.  It is not very convenient to do so and it defeats the purpose of automation.   Therefore, adding the EXIT FOR condition may be a better way of programming.

 

FOR loop with EXIT FOR condition

 

The syntax is:

For n = <begin looping value> To <end looping value>  

               …….

               If <condition> = True (or False) Then

                              Exit For

               End If

Next n

Below is the entire VB module for the calculation.  Please note that now I can make the end looping value to be an arbitrarily larger number (100 in the below example).  The FOR loop will be prematurely terminated when an empty cell is encountered (the condition) before reaching the end looping value.

Sub ForExitLoop()

    Dim n As Integer, SumTotal As Long

   

    n = 1

    SumTotal = 0

    

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    For n = 1 To 100

        SumTotal = SumTotal + ActiveCell.Value

        ActiveCell.Offset(1, 0).Select

       

        If IsEmpty(ActiveCell.Value) = True Then

            Exit For

        End If

    Next n

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

DO Loop

 

DO loop will continue running until certain condition is met. 

The syntax is:

Do

               …..

Loop Until <condition> = True (or False)

Sub DoLoop()

    Dim SumTotal As Long

 

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    Do

        SumTotal = SumTotal + ActiveCell.Value

        ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Value) = True

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

Below is the VB module of using DO loop.

DO loop can be combined with WHILE and UNTIL Conditions.  Below are the two variations of a DO loop.  Please note that the condition to be used in a WHILE and UNTIL loop will usually be the opposite of each other.  For example, if your DO WHILE condition is FALSE, the DO UNTIL condition should be TRUE.

 

DO loop with WHILE condition

 

The syntax is:

Do While <condition> = True (or False)

               …….

Loop

The entire VB module is as below:

Sub DoWhileLoop()

    Dim SumTotal As Long

 

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    Do While IsEmpty(ActiveCell.Value) = False

        SumTotal = SumTotal + ActiveCell.Value

        ActiveCell.Offset(1, 0).Select

    Loop

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

DO loop with UNTIL condition

 

The syntax is:

Do Until <condition> = True (or False)

               …….

Loop

The entire VB module is as below:

Sub DoUntilLoop()

    Dim SumTotal As Long

 

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    Do Until IsEmpty(ActiveCell.Value) = True

        SumTotal = SumTotal + ActiveCell.Value

        ActiveCell.Offset(1, 0).Select

    Loop

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

For the use of multiple looping mechanism (loop within loop), please refer to this article (FIFO and LIFO Calculations) for more information. 

 

 

Error Handler

 

It is always a good idea to include an error handler in the Excel VBA looping.  The error handler is placed for the below reasons:

  • to avoid the looping going on forever (i.e. an infinite loop);
  • to locate the errors;
  • to convey an error message to the users and provide possible solutions;
  • to prompt the users for further actions;
  • to automate the corrections by other VB codes;
  • to ignore or skip the errors.

When the loop hits an error, like a cell with missing data, incorrect data type, or exceed certain limit, the error handler should kick in and stop the looping.  If there is no error handler, Excel VBA will show an error message.  Unfortunately, many Excel error messages do not provide clear and adequate information to the users.  It is better to tailor make the error messages and provide possible solutions to the users to take corrective measures.

pic2 looping

There are two major ways to handle the errors: Prompt Actions or Ignore.  The Error Handler to prompt actions has the below syntax:

On Error GoTo ErrorHandler:

……

Exit Sub

ErrorHandler:

<VB codes for Error Handler>

The below example of VB codes illustrates that when an invalid type of data is encountered, the VB codes will highlight the error data with yellow color and prompt the users to correct such error data with an tailor-made error message.  Please note that you need to put the “Exit Sub” statement right before the ErrorHandler, otherwise, the VB compiler will run through those codes at the end no matter if the error trigger is hit.  You can name the ErrorHandler anything you want.  For example, you can name it as InvalidFormatError after the GoTo keyword, but please remember to call it the same way at the end of the VB module.

Sub ForLoopWithErrorHandler()

    Dim n As Integer, SumTotal As Long

   

    n = 1

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    For n = 1 To 100

        SumTotal = SumTotal + ActiveCell.Value

        On Error GoTo ErrorHandler

       

        ActiveCell.Offset(1, 0).Select

               

        If IsEmpty(ActiveCell.Value) = True Then

            Exit For

        End If

    Next n

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

    Exit Sub

   

ErrorHandler:

    With Selection.Interior

        .Color = 65535

    End With

    MsgBox ("Data contain invalid format.  Please correct them and try again."), vbOKOnly

End Sub

The ErrorHandler on the above example may miss one condition in Excel.  When a number is stored as text, the ErrorHandler may treat it as a number so the error will not show up and be handled as an error.  If you want the VB module to check this type of error, you need to include one more condition in your VB module:

If Application.WorksheetFunction.IsText(ActiveCell) = True Then GoTo ErrorHandler

Please note that you can skip typing “= True” if you want to.  So the entire VB module will be as follow:

Sub ForLoopWithErrorHandler()

    Dim n As Integer, SumTotal As Long

   

    n = 1

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    For n = 1 To 100

        If Application.WorksheetFunction.IsText(ActiveCell) = True Then GoTo ErrorHandler

       

        SumTotal = SumTotal + ActiveCell.Value

        On Error GoTo ErrorHandler

       

        ActiveCell.Offset(1, 0).Select

               

        If IsEmpty(ActiveCell.Value) = True Then

            Exit For

        End If

 

    Next n

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

    Exit Sub

   

ErrorHandler:

    With Selection.Interior

        .Color = 65535

    End With

    MsgBox ("Data contain invalid format.  Please correct them and try again."), vbOKOnly

End Sub

If you want the VB module to ignore the error data and keep going with the data with correct format, you can use the RESUME NEXT statement instead.  With the RESUME NEXT statement, you do not need to write any code for the ErrorHandler.  However, ignoring errors may undermine the integrity of the data set.  The VB module simply regards that the errors did not even exist.  Please make cautions when you want to do so. The VB codes for the module will be as follow:

Sub ForLoopIgnoreError()

    Dim n As Integer, SumTotal As Long

   

    n = 1

    SumTotal = 0

   

    Range("A18").Select

    Selection.ClearContents

   

    Range("A4").Select

    For n = 1 To 100

        SumTotal = SumTotal + ActiveCell.Value

        On Error Resume Next

       

        ActiveCell.Offset(1, 0).Select

                

        If IsEmpty(ActiveCell.Value) = True Then

            Exit For

        End If

    Next n

   

    Range("A18").Select

    ActiveCell.Value = SumTotal

End Sub

There is an additional way for ErrorHandler.  The statement is ON ERROR GOTO 0 (zero).  It is the default mode in VBA to indicate a run time error has occurred and display its standard run time error message box.  It is the same as having no error handler.  Any error will cause VBA to display its standard error message box.  I found that it is essentially a waste of time to even put it in the VB module.