Worksheetfunction vs UDF

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.

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

7 Responses to Worksheetfunction vs UDF

  1. mrt says:

    Being picky -

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

  2. dougaj4 says:

    mrt – OK, fixed.

    And congratulations on getting the first comment in :)

  3. mrt says:

    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

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s