Profiling and Optimizing VBA

In this article, I will show how to implement a code profiler, and use it to look at how to identify (and bust some myths) on what is fast and what is slow in VBA. The downloadable example provided is not only be a full code profiler that you can add to your project, but also has an 'automatic profiler' capability that can automatically insert (and remove) profiling code into your VBA project.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.