×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MAX functions in VBA

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!

RE: MAX functions in VBA

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/
 

RE: MAX functions in VBA

(OP)
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.
 

RE: MAX functions in VBA

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)

RE: MAX functions in VBA

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/
 

RE: MAX functions in VBA

Here is one test:

CODE

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources