What is code profiling
We all want to write efficient code, but just reading up on what is 'good' and what
is 'bad' doesn't help when your procedures are performing poorly and you need
to find out why. There are also a lot of myths out there about what is 'bad'
and what is 'good', so an effective profiler can help you separate out the realities.
In a previous article, I covered how to detect poorly performing worksheets, and how to identify which
cells were causing the recalculcuation speeds to be poor. Here we are going to
dive into profiling VBA code to find out which procedure or lines of code need
attention, and answering questions such as 'how long does procedure x take to
run', 'what percentage of the total time is spent in this piece of code', 'how
many times is function y executed', and so on. As usual a fully functional example
is available for download, but please see the section on installation below. You'll need all the files in
the optimzation section.
How does code profiling work
It is a very simple concept. Code is added at the start and end of the code needing
to be investigated, and various statistics are accumulated and reported at the
end. With this profiler, you can add the the code manually, or you can use the
autoprofiler which automatically adds code in procedures you specify. It's important
to note that you may want to use the profiler to leave code permanently in your
project. For example you could use it to stop processing after a certain amount
of time, or when a procedure had been executed 'n' times. In this article I am
going to focus on the autoprofiler to get an overview of how these kind of techniques
can be a applied.
Structure of profiled code
Here is a very simple typical layout with timing code injected around the code in
the main procedure as well as in a couple of sub procedures. You can have as
many timing sections in a procedure as you wish, but this would be typically
how you might start to identify overall statistics, as well as stats for individual
modules

Installing the profiler
For the purposes of this article, I have created a test worksheet with some simple
code that we are going to profile. That's called testProcProfiler.xlsm. The profiler
itself is called ProcProfiler.xlam. You should download all the files from the Optimization Section. Since it is an .xlam addin, we can
add that to any project you want to profile. Open testProcProfiler.xlsm, then
open procprofiler.xlam. To be able to access the cProcProfiler Class, which is
in the addin, you need to add a reference to it in your main workbook, in this
case, the testprocprofller.xlsm. To do that, go into tool/references for the
workbook project in the VBA IDE and select the cpProfiler reference as follows.

If all is good, then your project explorer will look like this, with the reference
included.

Selecting code for autoprofiling
We now want to bring up the procedure selection form from the addin, so navigate
to the apPublic module and execute the following procedure, which is in the apPublic
module of cProcProfiler.
Public Sub autoProfileForm()
apManageForm.Show
End Sub
Alternatively, you can run call cpProfiler.autoProfileForm from within your local
project.
This will bring up a form as follows, and you should select each procedure that you
want to profile. In this case, select them all. You also need to select your
'main procedure', (equivalent to the first row in the earlier diagram showing
the profiler structure), and somewhere to report the statistics to.

How it works
As mentioned previously, profiling is really about injecting code that collects timing
information at appropriate spots in your code. The autoprofiler accesses the
VBE model itself and updates your code. There are 3 classes you can look at,
cCapvProj, capvProc and capVModule which take care of accessing the VBE model, so examine those if you are interested
in finding out how that is done. To be able to do this it needs to be allowed
to access the VBE, so in the trust center under Excel options, make sure this
is ticked, and in the autoprofile form select the 'Add Autoprofile Code' button

This will introduce profiling code throughout as below.
Your main module
Sub testProfiler()
Call cpProfiler.apConstruct("apProfiler_1") 'apProfiler_1 :apr:mgr:0.2
on 2/28/2011 at 12:04:57 PM
cpProfiler.apProfiler_1.StartProfiler "apProfiler_1" 'apProfiler_1 :apr:mgr:0.2
on 2/28/2011 at 12:04:57 PM
cpProfiler.apProfiler_1.Start "Proc:testProfiler", "(TestProfiler)
Proc:testProfiler" 'apProfiler_1 :apr:slc:0.2 on 2/28/2011 at 12:04:57
Dim i As Long, r As Range, d As Double
Set r = Range("sheet1!a1")
d = Rnd()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To nTimes
putaValue r, d
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
cpProfiler.apProfiler_1.Finish "Proc:testProfiler" 'apProfiler_1 :apr:slc:0.2
on 2/28/2011 at 12:04:57 PM
cpProfiler.apProfiler_1.FinishProfiler 'apProfiler_1 :apr:mgr:0.2 on 2/28/2011
at 12:04:57 PM
cpProfiler.apProfiler_1.Results Range("'Sheet2'!$A$1") 'apProfiler_1
:apr:mgr:rng|'Sheet2'!$A$1|:0.2 on 2/28/2011 at 12:04:57 PM
cpProfiler.apProfiler_1.DestroyTimers 'apProfiler_1 :apr:mgr:0.2 on 2/28/2011 at
12:04:57 PM
End Sub
In each profiled procedure
Private Sub putaValue(r As Range, d As Double)
cpProfiler.apProfiler_1.Start "Proc:putaValue", "(TestProfiler)
Proc:putaValue" 'apProfiler_1 :apr:slc:0.2 on 2/28/2011 at 12:04:57
r.Value = d
cpProfiler.apProfiler_1.Finish "Proc:putaValue" 'apProfiler_1 :apr:slc:0.2
on 2/28/2011 at 12:04:57 PM
End Sub
If you startup the form again, you will see that it remembers what has been selected
for profiling. It does this by analyzing the code in your project and recognizing
what it has inserted versus what you might have inserted. You should therefore
not amend any of the comments associated with automatically inserted code. You
can easily remove all autoprofile code by selecting the 'remove AutoProfile code'
button on the form
Interpreting the results
When you run your main procedure, on completion, the report will show up on the sheet
specified in the form and looks like this

It should be self explanatory what these are, and from it we can see the number of
seconds spent in each section, the number of times it was called and the %age
of time spent in each section.
Some surprises
For the purposes of illustration, I looked at a few random questions on various forums
where people had been given strong advice on how to optimize their VBA code and
tried them out (not very scientifically)
Advice 1: Some complex math operations take ages to execute. Try to avoid repeating calculations.

It took about .7 seconds to do the most complicated math I could think of 100,000
times., including a couple of rnd() operations, as below
Const nTimes = 100000
For i = 1 To nTimes
doSomethingComplicated
Next i
Private Sub doSomethingComplicated()
Dim d As Double
d = Sqr(Exp(Sqr(Tan(Sqr(CDbl(Rnd() + 0.1)))))) / Log(0.1 + Rnd() * Sqr(Exp(Sqr(Tan(Sqr(CDbl(Rnd()
+ 0.1)))))))
End Sub
Contrast that with putting a value into a cell, 100,000 times as below, which took
about 13 seconds, even with screen updating off, and no formulas in the spreadsheet.
d = Rnd()
Application.ScreenUpdating = False
For i = 1 To nTimes
putaValue r, d
Next i
Application.ScreenUpdating = True

Conclusion - although it seems logical and good practice to avoid repeating complex
maths operations, a single cell operation is much more of fruitful area of optimization.
See this article for a group of classes that abstract cell data from their location and can therefore
provide a big performance boost
Advice 2: range.cell(x,y), and range.offset(x,y) are more expensive operations than range.value, with range.offset(x,y) being the worst.
Const nTimes = 100
Const nRows = 100
Application.ScreenUpdating = False
For i = 1 To nTimes
doCellTestNormal r
Next i
Application.ScreenUpdating = True
Private Sub doCellTestNormal(r As Range)
doCellTest r
End Sub
Private Sub doCellTest(r As Range)
useCells r
useOffset r
useReset r
End Sub
Private Sub useCells(r As Range)
Dim i As Long
For i = 1 To nRows
r.Cells(i, 1) = i
Next i
r.Worksheet.UsedRange.Clear
End Sub
Private Sub useOffset(r As Range)
Dim i As Long
For i = 1 To nRows
r.Offset(i, 1) = i - 1
Next i
r.Worksheet.UsedRange.Clear
End Sub
Private Sub useReset(r As Range)
Dim ro As Range, i As Long
Set ro = r
For i = 1 To nRows
ro.Value = i + 1
Set ro = ro.Offset(1)
Next i
r.Worksheet.UsedRange.Clear
End Sub
3 procedures, doing the same thing using the 3 different approaches, all gave similar
result. about 1.5 seconds for 10000 cell operations, with range.value being only
very marginally more efficient.

Conclusion - no reall difference. Use whatever makes sense for clarity.
Advice 3: Application.Screenupdating makes a huge difference in execution speed.
The previous test had screenupdating turned off.. lets just repeat with it turned
on.

So rather than the expected improvement, it was 16% worse. If you turn it off and
on many times, rather than just once at the beginning and once it gets a massive
69% worse.
For i = 1 To nTimes
doCellTestInvisible r
Next i
Private Sub doCellTestInvisible(r As Range)
Application.ScreenUpdating = False
doCellTest r
Application.ScreenUpdating = True
End Sub

In the previous tests, the sheet that was being updated was obscured by the VBA IDE,
this time I revealed the sheet actually being updated and turned screen updating
on. From 3.9 seconds when we had it turned on, but not active, to 6.5 with it
active. (still less than screen updating being turned off and on)

Conclusion: Clearly there is a lot of overhead associated with turning screen updating
off and on, to the extent that even with 10000 cell operations, the overhead
was more than the potential benefit. The most efficient solution seems to be
to leave screen updating on, but make sure the current sheet is not active.
Advice 4: Turn automatic calculation off.
This one should be a no-brainer. Let's try put cell operations with calculation turned
off and compare to the earlier example.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To nTimes
putaValue r, d
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Thats a 65% performance increase right there. And there are no formulas in the workbook
(note to Microsoft .. that's kind of scrappy ).
Conclusion: : Always turn off automatic calculation, even if there are no formulas
in your workbook.
Removing autoprofiling code
Just bring up the Autoprofile form again and sleect the remove autoprofile form and
all the injected code will be removed. To remove the .xlam reference you remove
the reference to cpProfiler in options/references.
Coding it yourself
In this article I've focused on autoprofiling here, which is just an form overlay
that provides a quick profile of your procedures. For a more detailed code investigation
you may want to add your own profile points, and do some deeper dives. The downloaded
cProcProfiler class is fully functional and has all the necessary methods and
properties required to be build your own tool. See here for more details on how to use.
Summary
I've just scratched the surface on optimization here, but one of the key things is
that to be able to optimize you need to measure each tweak you make, and not
rely on subjectivity. I hope you find these tools useful, and as always I look
forward to your feedback, questions, code contributions or improvements or anything
else. It would be nice to see 'things you've found out', for example, appearing
in the FAQ section of this forum. You can find me at excel@mcpher.com, on this
forum, or at www.mcpher.com
Acknowledgement
Thanks for the insight on how on earth to get into the VBE to be able to inject code
from Chip Pearsons' site , and to Charles Williams, Decision Models Limited on how to get a more accurate timer.