Testing the speed of sections of VBA code?

[GBagley]

Anyone have a great way of testing the speed of certain sections of VBA code? I have a program that takes some time and goes through a ton of loops. I'm interesting in testing each section of the code to see which parts take the longest to run through. Which lines/formulas take the longest. For example, if my code takes 30 minutes to run in full, and one particular section accounts for 90% of that time, then I would want to focus in on improving just that part. I know I could have the code print times after every line, but it's already a lengthy macro as is, and it seems like there must be a better way to do a speed analysis?

 

[Peter_SSs]

Try something like this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SpeedTest()<br>    <SPAN style="color:#00007F">Dim</SPAN> dStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, dEnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>       <br>    <br>    dStart = Now<br>    <br>    <SPAN style="color:#007F00">'First section to test</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 10000<br>        Range("A1").ClearContents<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br>    dEnd = Now<br>    MsgBox "Section 1 took about " & (dEnd - dStart) * 86400 & " seconds"<br>    <br>    <br>    dStart = Now<br>    <br>    <SPAN style="color:#007F00">'Second section to test</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 15000<br>        Range("A1").ClearContents<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br>    dEnd = Now<br>    MsgBox "Section 2 took about " & (dEnd - dStart) * 86400 & " seconds"<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Zack Barresse]

Just use two variables, one which sets as Time when the sub starts, and another before your sub ends, then compare the two. But I would submit if your sub takes 30 min to run, the code can be made quite a bit more efficient. Post all of your code, along with some scope information about it.

 

[GBagley]

Can't really post it here, but it's just under 5000 lines of code, split across maybe 10-15 modules, and a bunch of userforms. Fairly complex. I've already built in some benchmarking that logs the start/end times for each of the main sub procedures (used to take up to 2 hours to run, but I've gotten it down to about 25 minutes now), so now I want to dig in further an get more of a line-by-line analysis. Like I know worksheetfunctions take longer to calculate than in-built VBA functions. And reading/writing to and from the worksheet takes longer than manipulating variables from within the code. So I've been trying to keep those kinds of things in mind and modify anything I can to make it more efficient. The main procedure runs through a loop of up to 50k iterations, so if there is just one line that takes significantly longer than the others, I would like to find it

 

[Peter_SSs]

So you should be able to use the idea I posted (and Zack also suggested) to test any section (or line) you want.

Have you also turned the ScreenUpdating off while the code is running?

 

[Zack Barresse]

Have you also turned the ScreenUpdating off while the code is running?

This is one of the reasons we need to see the code. I'm willing to bet there are a great many instances of inefficiency (as there generally is).

 

[Peter_SSs]

This is one of the reasons we need to see the code. I'm willing to bet there are a great many instances of inefficiency (as there generally is).

You may well be right, but we really don't want 5000 lines of code posted.

Possibly some smaller sections and if we can see efficiency gains in those, then the principles could be applied across the whole code by the OP.

 

[GBagley]

I have a userform option that allows the end user to turn screen updating on or off. In the benchmarking I did, I was surprised by what little difference it made. Maybe shaved off a minute or two at most for the longer procedures.

Anyways, the purpose of this thread isn't to have you all analyze my code (thanks though!) - I'm just looking for a better way to find the inefficiencies myself - aka, a way to precisely test how long each line of code takes to run through. Is it worth putting a timestamp log after every line of code, or would that even be reliable enough get an accurate picture? Is there a VBA time function that would give a measurement more precise than seconds?

 

[Peter_SSs]

If it were me, I definitely wouldn't be looking at every one of the 5,000 lines! I would look at reasonably large sections. For example, if you have a loop like in my sample, it isn't worth measuring the time for Range("A1").ClearContents 10,000 times. You might as well just get an idea of the time for the loop to complete 10,000 cycles.

If you think a particular section is worth investigating further, then test various sub-sections within that section etc.

 

[brettdj]

I have a program that takes some time and goes through a ton of loops.

If these are range loops then evil, evil, evil ......
Try using AutoFilter, Specialcells, or in particular variant arrays instead

A short sample of your loops (not the entire code) may be worth posting

The advice from Peter and Zack above should suit you fine for what you have asked for timing. I do recommend the perfmon utility that comes with Professional Excel Development as being excellent for prodcedure time measurement if you code is split into many subs - a good excuse to buy the book if you don't already have it

Cheers

Dave