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.
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 =
…….
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 =
…….
If
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
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
…….
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
…….
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.
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:
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.
