Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Exiting a For ... Next Loop Early.
If you use For ... Next loops in your macro programming (who doesn't?), then you should know that they can take a great deal of time. You can minimize this by only checking what you need. For instance, consider the following code, which checks an array to see if a value exists. If it doesn't, then it adds the value to the end of the array. If it does, then the value is not added.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
AddIt = False
For J = 1 to NumEntries
If NumValues(J) = ToAdd Then AddIt = True
Next J
If AddIt Then
NumEntries = NumEntries + 1
NumValues(NumEntries) = ToAdd
End If
This works great, but if the array gets large, you can end up going through the For ... Next loop quite a few times. Now consider the following code, which accomplishes the same task, but dumps out of the For ... Next loop early if a match is detected.
AddIt = False
For J = 1 to NumEntries
If NumValues(J) = ToAdd Then
AddIt = True
Exit For
End If
Next J
If AddIt Then
NumEntries = NumEntries + 1
NumValues(NumEntries) = ToAdd
End If
Now if a match is found early on in the loop, all the rest of the iterations are skipped because the Exit For statement is encountered and the loop is basically exited right away. The result is a faster running macro.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11335) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Exiting a For ... Next Loop Early.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Perhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to ...
Discover MoreDo you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. ...
Discover MoreNeed to specify which directory on your hard drive should be used by a macro? It's easy to do using the ChDir command.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-05-21 13:24:56
JMJ
Yes, Doug is right: the test is reversed!
2014-12-01 07:21:49
Doug
Just a note to say that the Addit conditions are reversed in the code. It should be initialized to TRUE and set to FALSE if the value is detected in the loop.
2014-12-01 07:12:18
While it's not a good programming practice, I prefer going in and out of loops by using GoTo.
It's useful when you have a "loop of loops", or:
For i = 1 to n
For j = 1 to m
[stuff]
If (whatever) Then GoTo skiploop
Next j
For j = 1 to o
[otherstuff]
If (whatever) Then GoTo skiploop
Next j
skiploop:
Next i
The proper way to do this would be with embedded Ifs or with Booleans used as checks (as in this tip's example), but it's usually too bothersome.
And for big loops it can add too many extra operations that have an effect in the execution time (I'm thinking bruteforcing Project Euler-like stuff; in small loops of, say, <10k iterations you won't see the effect).
Yay for laziness.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments