MAX functions in VBA

[yakpol]

If anybody has experimented which of MAX functions is faster in VBA
WorksheetFunction.Max() or
MAX = iif(A>=B,A,B) or
if A >= B then MAX = A else MAX = B

Thank you!

 

[IDS]

I haven't checked this function, but I'd be amazed if using the worksheetfunction wasn't much slower than the other two, especially with XL2007.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[yakpol]

Well, I ran a test for 10,000,000 operations

WorksheetFunction.Max() took 20.64 sec
MAX = iif(A>=B,A,B) took 4.53 sec
if A >= B then MAX = A else MAX = B took 1.84 sec

IDS, you were absolutely right, worksheet function is the slowest. But it works very fast with arrays. To take 1000 times maximum of 10,000 long arrray took 4.56 seconds. As I understand the function call itself takes a long time.

 

[handleman]

In general, using a WorksheetFunction should be faster for more complex operations, because the worksheet functions run in the compiled code which has already been optimized by Excel programmers. However, using the worksheet function for getting the greater of 2 different numbers is certainly overkill. I would imagine if you wrote your own complete MAX function in VBA to handle that same 10,000 member array it would probably run slower than the worksheet function.

-handleman, CSWP (The new, easy test)

 

[IDS]

Yakpol - yes it's the function call that's the killer.

handleman - there are no doubt exceptions, but in my experience use of worksheetfunction on a single parameter is almost always much slower than writing your own UDF, even for complex functions. For instance it is much quicker to write your own version of trigonometric functions that aren't provided in VBA, rather than use the Excel version via worksheetfunction. This blog post gives more details:

http://newtonexcelbach.wordpress.com/2008/02/23/worksheetfunction-vs-udf/

The figures there are pretty typical in my experience; worksheetfunction is about 10 times slower with XL2007 and about 3 times slower with previous versions.

As noted by Yakpol, if you use worksheetfunction with a large array, so the function call is only made once, it is a different story.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Here is one test:

Sub mytimer()
Dim StartTime As Double
Dim i As Long
Dim mymax As Double
Dim num1 As Double
Dim num2 As Double

    num1 = Sqr(2)
    num2 = Application.WorksheetFunction.Pi

    ' Test the excel max(0 function
    StartTime = Timer
    For i = 1 To 100000
        mymax = Application.WorksheetFunction.Max(num1, num2)
    Next i
    Debug.Print "Time for excel Max is ", Timer - StartTime

    ' Test the vba iif function
    StartTime = Timer
    For i = 1 To 100000
        mymax = IIf(num1 > num2, num1, num2)
    Next i
    Debug.Print "Time for vba iff is ", Timer - StartTime

    ' Test the vba if branch assignment
    StartTime = Timer
    For i = 1 To 100000
        If num1 > num2 Then
            mymax = num1
        Else
            mymax = num2
        End If
    Next i
    Debug.Print "Time for vba if branch is ", Timer - StartTime
End Sub

Results for this code on my machine are:

Time for excel Max is        0.375625000000582 
Time for vba iff is          3.18750000005821E-02 
Time for vba if branch is    8.74999997904524E-04

=====================================
Eng-tips forums: The best place on the web for engineering discussions.