Performance Monitor

Professional Excel Development

Professional Excel Development has a chapter on optimization that discusses the PerfMon utility (available on the companion CD). I used it for the first time on a 40 second process and I thought I would share the results. Thrilling, I know.

One hundred fifty thousand calls to class properties? Yikes! Noting that FillFinals was the biggest culprit, I manually added some perfmon calls inside that procedure to see what I could see.

Inserting the final reports consists of adding sheets to the final report workbooks, among other things. In this case it adds nine sheets to six different workbooks. I decided to break up that block of code even further. Specifically, I wanted to isolate the Sheets.Add line.

I guess adding sheets takes a lot of time. Maybe I should create a report with some ‘final reports’ already in it so I can limit the amount of sheets that I have to add. Of course I’ll have to delete extraneous sheets, so I’ll have to weigh the costs of that. Well, nothing earth shattering here. It was just the first time I used it on a real program and it was fun.

A couple of bugs I noted in the utility:
It puts PerfMonProcEnd statements before any Exit Sub statements, but when it deletes them it doesn’t respect my original tabbing.
My manual lines looked like PerfMonProcEnd “FireAssay.MProcess.FillFinals.HeaderData”. I don’t think I was supposed to put a period after FillFinals (the procedure name) because the output file added another column. That’s OK, but it didn’t adjust the headers. In the screen shots above, I manually adjusted the headers and added a Section header. It’s probably user error rather than a bug.

Posted in Uncategorized

10 thoughts on “Performance Monitor

  1. The results given below didn’t use the PED performance monitor (although they could have), but I’ve been looking for an excuse to share them, and this looks as good as any.

    I’ve been working on some interpolation functions, which require finding the position of the interpolation value in a list, so I did a bit of benchmarking.

    The results below are for finding the position of a number in a table with 10,000 entries, looping from 1 to 10,000

    Starting with the slowest:

    Passing the table data as a range, and using a brute force search: 231 seconds.

    Converting the range to an array: 6.5 seconds, 35 times faster, not bad!

    Using Worksheetfunction.match instead of the brute force search, with an array: 20.7 seconds, hmm disapointing.

    Using Worksheetfunction.match, with a range: 0.171 seconds, wow! That’s 120 times faster than match with an array, and 1,350 times faster than brute force with a range!

    Using a simple UDF in place of brute force, with a range: 0.16 seconds; not really worth the effort.

    Using the same UDF , but with an array: 0.014 seconds; 12 times faster than the previous best, and over 16,000 times faster than the slowest!

    So the moral of this tale is:

    If you are using worksheetfunction.match (or .vlookup), use a range not an array.

    If speed is important consider writing your own lookup function, it doesn’t need to be complicated to give a big speed improvement.

    If you do write your own function, use an array not a range.

    Don’t even think about using a range with a brute force search!

    As far as other worksheetfunctions are concerned, try it and see. My limited experience suggests that arrays are usually slightly faster than ranges, but built in VBA functions are mutch faster than calling a worksheetfunction.

  2. OK

    Remember that I just put this together quickly for testing purposes, so it isn’t properly tested, and it probably isn’t particularly efficient either.

    Function VBAMatch(arg As Double, XRange As Variant) As Long
    Dim x1 As Double, x2 As Double, xslope As Double
    Dim MaxRow As Double, MinRow As Double
    Dim row1 As Long, row2 As Long, rownext As Long
    Dim Diff As Double

    ' Convert Xrange to an array if passed as a range
    If TypeName(XRange) = "Range" Then XRange = XRange.Value

    MinRow = 1
    MaxRow = UBound(XRange)

    row1 = 1
    row2 = MaxRow

    Do While MaxRow - MinRow > 4
    x1 = XRange(row1, 1)
    x2 = XRange(row2, 1)
    If x2 = arg Then
    VBAMatch = row2
    Exit Function
    End If
    If x2 > arg Then MaxRow = row2 Else MinRow = row2
    xslope = (x2 - x1) / (row2 - row1)
    rownext = row2 + Int((arg - x2) / xslope)
    If rownext MaxRow Then rownext = MaxRow
    row1 = row2
    row2 = rownext
    If row2 = row1 Then Exit Do

    Diff = 1
    row2 = MinRow
    Do While Diff > 0 And row2

  3. Oops, I should read the instructions:

    Do While Diff GT 0 And row2 LT MaxRow
    row2 = row2 + 1
    Diff = arg – XRange(row2, 1)

    If Diff LT 0 Then
    VBAMatch = row2 – 1
    VBAMatch = row2
    End If
    End Function

    Replace GT and LT with the appropriate symbols.

  4. A bit more on UDF’s vs Worksheetfunction.

    ATan2 is a very useful function, for engineers and scientists, and anyone else who is concerned with computations that involve the directions of lines or vectors. What it does is give you the direction of a line based on the difference between the x and y values of any two points on the line. Importantly it gives an angle over the full 360 degree range of possibilities, not just 180 degrees as ATan does.

    Considering this is such an important function it is surprising that VBA doesn’t provide it, but it doesn’t. Having seen the dramatic difference in speed between worksheetfunction.match and a UDF match I decided to write my own ATan2 function. Here it is:

    Function VBAATan2(ByVal DX As Double, ByVal DY As Double) As Variant

    Const Pi As Double = 3.14159265358979

    If DY LT 0 Then
    VBAATan2 = -VBAATan2(DX, -DY)
    ElseIf DX LT 0 Then
    VBAATan2 = Pi – Atn(-DY / DX)
    ElseIf DX GT 0 Then
    VBAATan2 = Atn(DY / DX)
    ElseIf DY LTGT 0 Then
    VBAATan2 = Pi / 2
    VBAATan2 = CVErr(xlErrDiv0)
    End If

    End Function

    Hope that works. Repla

    Times for looping through a list of 1000 calculations 1000 times (ie 1 million calculations) in XL 2007 are:

    UDF: 0.73 sec
    Worksheetfunction.atan2: 11.42 sec

    So a better than 15X improvement in speed.

    An added benefit is that my UDF returns a DIV/0 error when passed 0,0 whereas the worksheetfunction just doesn’t work, so you have to check for the 0,0 before calling the function. That’s a bit strange because ATAN2() does give DIV/0 with 0,0 when used directly in the spreadsheet. I’d be interested if anyone has any ideas about the reason for the difference in behaviour.

    Also of interest to anyone contemplating changing to XL2007 (or going back to an earlier version!) with XL2000 the times are:

    UDF: 0.73 sec
    Worksheetfunction.atan2: 2.11 sec

    The VBA is exactly the same speed in the earlier version, but the worksheetfunction call is over 5 times quicker! So in earlier versions you still get a respectable speed improvement with a UDF but not so dramatic as with 2007.

  5. Hi,
    I’m evaluating Office 2010 beta 64bit on Win7 64 bit.
    I can’t get PerfMon to work. It doesn’t seem to register correctly. This is the installscript:
    *** Installation Started 12/07/09 10:54 ***
    Title: Performance Monitor v1.0 Installation
    Source: I:softwareDeveloper SoftwareVBAPerfMonPerfMon.EXE
    Made Dir: C:Program Files (x86)Office Automation
    Made Dir: C:Program Files (x86)Office AutomationPerfMon
    File Copy: C:Program Files (x86)Office AutomationPerfMonUNWISE.EXE
    RegDB Key: SoftwareMicrosoftWindowsCurrentVersionUninstallPerformance Monitor v1.0
    RegDB Val: Performance Monitor v1.0
    RegDB Name: DisplayName
    RegDB Root: 2
    RegDB Key: SoftwareMicrosoftWindowsCurrentVersionUninstallPerformance Monitor v1.0
    RegDB Name: UninstallString
    RegDB Root: 2
    File Copy: C:Program Files (x86)Office AutomationPerfMonPerfMonitor.dll
    File Copy: C:Program Files (x86)Office AutomationPerfMonPerfMonOffice.dll
    File Copy: C:Program Files (x86)Office AutomationPerfMonPerfMonVB6.dll
    File Copy: C:Program Files (x86)Office AutomationPerfMonCPerfMon.cls
    File Copy: C:Program Files (x86)Office AutomationPerfMonPerfMon ReadMe.txt
    Self-Register: C:PROGRA~2OFFICE~1PerfMonPERFMO~1.DLL
    Could not Self-Register: C:PROGRA~2OFFICE~1PerfMonPERFMO~2.DLL
    Could not Self-Register: C:PROGRA~2OFFICE~1PerfMonPERFMO~3.DLL

    any ideas?

  6. @Jurgen

    I had this problem with Office 2010 32 bit running on Win-7 64 bit. Icould not get the dll to register using regservwhile the files were located in the C:Program Files (x86)Office AutomationPerfMon

    I copied the PerfMon folder to C:windowssystem and was able to register the files using RegServ (e.g.: Regsvr32 C:WindowssystemPerfMonPerfMonVB6.dll )

    Realize this is late reply but first time on this thread.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.