Worksheetfunction vs UDF

Posted on February 23, 2008 by

The previous post provided VBA code for a number of trigonometric functions, most of which are already available in Excel.  Why bother you may say, why not just use the WorksheetFunction object?  The numbers below provide the answer:

Time to calculate ATan2() 65,536 x 16 times

XL2007

XL2000
VBA ATn2() 

1.60

1.21

Worksheetfunction

14.20

3.06

With Excel 2007 using a UDF in preference to Worksheetfunction gives a speed improvement of almost 10 times.  With Excel 2000 the difference is not so dramatic, but still a worthwhile 2.5 times improvement.

This entry was posted in Excel, UDFs and tagged . Bookmark the permalink.

7 Responses to Worksheetfunction vs UDF

  1. mrt says:
    February 26, 2008 at 6:46 am

    Being picky –

    But not for spellers: An Excel blog for engineers and scientists, and an ->egineering<- and science blog for Excel users.

    Like

    Reply
  2. dougaj4 says:
    February 26, 2008 at 12:31 pm

    mrt – OK, fixed.

    And congratulations on getting the first comment in 🙂

    Like

    Reply
  3. mrt says:
    February 27, 2008 at 1:40 am

    Hi Doug –

    I followed the link here from Daily Dose of Excel, and I’ll be back, for I’m definitely not one of those $$$ guys.

    I really like your concept.

    …mrt

    Like

    Reply

  4. Pingback: Excel 2007 performance - feedback please « Newton Excel Bach, not (just) an Excel Blog

  5. Pingback: Worksheetfunction vs UDF - 2 « Newton Excel Bach, not (just) an Excel Blog

  6. Pingback: Excel VBA Performance in Office 2007 - Code For Excel And Outlook Blog

  7. Pingback: Pendiente compatibilidad 2003/2007 mucho mas lento

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

w
Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.