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. That’s all with XL2007 by the way. XL 2000 will not accept an array argument with worksheetfunction.match or .vlookup.

3. 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

4. Doug:
   Could you show us a bit more on the array UDF please?

5. 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

6. Last few rows:

   Do While Diff > 0 And row2

7. 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.

8. 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
   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.

9. 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?

10. @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.