Profiling user defined functions


Once you've analyzed your workbook for columns which are slow to calculate, it may be that the evidence is showing that some of your user defined functions need to be optimized. Or perhaps you have a complex set of functions you need to profile to find out which code sections are the least efficient. Here you will find a profiler you can use to analyze and report on your procedures. There is a downloadable add-in later, however you may instead want to move to the section on autoprofiling which will get you started with minimum effort.

Profiling Report example

We are trying to get to a report that looks like this. This will show every code section you have chosen to profile along with the results and the context. This should help you identify which areas of code to focus on.


Profiling concept

A simple profiling session might have a structure like this


At the simplest level, you can create one profiler, then within that as many timing sections as you like. A profiler is a group of code sections that should be reported and analyzed together. The time between starting and finishing the profiler is the elapsed time of the analysis, and all %ages shown for sections are based on that elapsed time.This will deal with most requirements and this article will be covering a scenario like that.

The example you download will work on Excel 2003 or 2007, and perhaps some other versions, and will contain the following-
  • cProcTimer - an instance of this class is created for every section
  • cProcProfiler - an instance of this class is created for every profiler. 
  • ProcProfileWrapper - this module provides a few procedures to set up the data structures, and provides a ready made public instance of cProcProfiler for you to use. It also contains an example as below
  • ptLevels - an enum allowing you to vary the depth of analysis without changing the profiling calls throughout your code

Example

Whereas many of the properties and methods contained in the classes can be accessed, there are only a small number that you will need for normal use, all of which are used in this code sample. This example is included in the downloadable attachment  or on the download page. Run exampleProfile to see what happens

Sub exampleProfile()
    Dim i As Long
    ' create defaultprofiler, and start
    Call procProfilerConstruct(, ptLevelAll)
    '
    ' ... some code
    '
    Call procProfiler.Start("Manager", "example Profile", ptLevelLow)
    '
    ' ... some code
    '
    ' pause timing to collect user input
    '
    Call procProfiler.Pause("Manager")
    '
    MsgBox ("restart timing")
    '
    Call procProfiler.Start("Manager")
    '
    ' some more code
    '
    For i = 1 To 100
        Call exampleprofilesub
    Next i
    '
    ' some more code
    '
    Call procProfiler.Finish("Manager")
    '
    ' all over
    '
    Call procProfiler.FinishProfiler
    '
    ' report results
    '
    Call procProfiler.Results(Range("ResultSheet!a1"))
    '
    ' clean
    '
    Call procProfilerDestroy
    '
End Sub
Sub exampleprofilesub()
'
' some more code
'
    Dim i As Long, d As Double
    Call procProfiler.Start("Sub", "exampleprofilesub", ptLevelMedium)
    '
    'some more code
    For i = 1 To 1000
        Call procProfiler.Start("Random loop", "exampleprofilesub", ptLevelHigh)
        d = Sqr(Rnd(i)) * Sqr(Rnd())
        Call procProfiler.Finish("Random loop")
    Next i
    '
    ' some more code
    '
    Call procProfiler.Finish("Sub")
End Sub
 

cProcProfiler Class

Your main interaction is with the cProcProfiler class. 

cProcTimer Class

For normal applications you should not need to access either the properties or the methods in this class. However we will cover these in a more advanced application later

ProcProfileWrapper Module

In order to minimize the the amount of profiling code needed in your procedures, and in particular to avoid the need to define any object, you can call these modules to construct a default profiler and clean up afterwards. 

ptLevels enum

You will see that both the cProcTimer class and the cProcProfiler class reference a level. The main use of this is enable you to filter which sections to profile without having to change any of your code, aside from the initial call to construct the profiler

For help and more information join our forum, follow the blog, follow me on twitter


You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.