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.
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.
That’s all with XL2007 by the way. XL 2000 will not accept an array argument with worksheetfunction.match or .vlookup.
Doug,
Thank you and Dick for sharing your findings and also.
It is much more impressive when it comes to numbers I like the numbers
Doug:
Could you show us a bit more on the array UDF please?
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
Loop
Diff = 1
row2 = MinRow
Do While Diff > 0 And row2
Last few rows:
Do While Diff > 0 And row2
Oops, I should read the instructions:
Do While Diff GT 0 And row2 LT MaxRow
row2 = row2 + 1
Diff = arg – XRange(row2, 1)
Loop
If Diff LT 0 Then
VBAMatch = row2 – 1
Else
VBAMatch = row2
End If
End Function
Replace GT and LT with the appropriate symbols.
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:
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
Else
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.
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 Val: C:PROGRA~2OFFICE~1PerfMonUNWISE.EXE C:PROGRA~2OFFICE~1PerfMonINSTALL.LOG
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?
@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.