MAX functions in VBA
MAX functions in VBA
(OP)
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!
WorksheetFunction.Max() or
MAX = iif(A>=B,A,B) or
if A >= B then MAX = A else MAX = B
Thank you!





RE: MAX functions in VBA
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: MAX functions in VBA
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.
RE: MAX functions in VBA
-handleman, CSWP (The new, easy test)
RE: MAX functions in VBA
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:
htt
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/
RE: MAX functions in VBA
CODE
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
CODE
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.