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!
Need to select a range of cells in a column? This tip can help, as it shows how to select from a specific cell all the ...
Discover MoreStrings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so ...
Discover MoreWant to create a summary worksheet that pulls a single row of data from each worksheet in the workbook? Here are a couple ...
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 © 2021 Sharon Parq Associates, Inc.
Comments