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.
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
A common part of working with text strings in a worksheet is normalizing those strings so that they follow whatever rules ...
Discover MoreWhen you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the ...
Discover MoreMacros are often used to process information in a worksheet. You may need your macro to change the values stored in ...
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