LinkBack URL
About LinkBacks
Are there any information how much different statements require CPU time?
What is for instance difference between While and For Next loop. Which one is more effective Select Case or IF. What is the fastest way to step counters etc.
Leevi
Are there any information how much different statements require CPU time?
What is for instance difference between While and For Next loop. Which one is more effective Select Case or IF. What is the fastest way to step counters etc.
Leevi
I have never seen any comparsions.
From experience it is all in the way you code & what you are doing within the loop
Personally I prefer to use For loops as they have a counter built into the command as Do & While loops you need to add a seperate command to add 1 to the counter variable
For tips on increasing macro speed have a look at
HTML Code:
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely
Top Excel links for beginners to Experts
If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assistedor failed to assist you
I welcome your Feedback.
I made a simple test program where I compared execution times:
For loop took ~20s and the corresponding while loop ~25s => While loop is 25% slower.
Leevi
You should post the exact statements that you ran. I would suspect that you could increase/decrease performace of either statement by simply not type declaring your variables, or using a counter vs not using a counter. Also, what you do inside the loop would make a huge difference too; even using boolean logic will be faster than using comparisons with the '=' operator.
There is a great MS-Excel book that dives into this very topic in one of its chapters. In addition, the books focus is on professional development of MS-Excel applications and provides a deep core understanding of the tool.
"Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA"
Authors: Stephen Bullen, Rob Bovey, and John Green
An excellent book and reference.
Reach me at excel_help at bellsouth dot net
I second Tarball's recommendation of Professional Excel Development, with the caveat that a lot of it (most of it) is not for beginners. They have a ton of information on best practices.
1. In terms of speed, iterating elements from a collection (For Each/Next) is fastest, followed by For/Next and Do/Loop. I would always use For/Next rather than Do/Loop unless there was a good reason not to; the code is usually easier to follow.
2. Select Case statements are prettier, If/ElseIf/EndIf statements are faster (curious; you'd think the compiled code would be the same). Either should be arranged to test cases from most likely to least.
3. Don't use Option Compare Text unless you have no other way.
4. Don't use wildcard comparisons (If s Like "*##*" Then) unless you have to.
5. The Len function is among Excel's fastest; all it has to do is look at the first word of a string, where the length is stored. So rather than If s = "", use If Len(s) = 0
6. Declare all variables appropriate to data type. Use Variants only when necessary.
7. Use early binding for all Objects unless you have specific reason not to.
8. Write logical, clear code first, and then optimize for speed. Obscuring the structure and intent of code for speed will have the person that has to maintain it later casting aspersions on your lineage.
Leevi
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks