Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Is WorksheetFunction really necessary??? (Excel 2000 >)

    I discovered that using the WorksheetFunction method is superfluous.

    The following statements have exactly the same result:
    Ans = Application.WorksheetFunction.Power(5, 3)
    Ans = Application.Power(5, 3)

    Is there something I am missing in the reason why you have both statements, or is this just one of those many ways that distinguish a novice from an expert??

    Any thoughts?
    Regards,
    Rudi

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Is WorksheetFunction really necessary??? (Excel 2000 >)

    Hi Rudi,

    Worksheet functions are useful where there is no direct vba equivalent. Also be aware that some worksheet function return completely different results from their vba equivalents - the vba DateDiff function and the worksheet DateDif function are classic examples of this.

    Cheers
    PS: For your example, Ans = 5^3 would also do the job!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is WorksheetFunction really necessary??? (Excel 2000 >)

    See <post#=489,112>post 489,112</post: > for an interesting difference between Application.WorksheetFunction.function and Application.function.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is WorksheetFunction really necessary??? (Excel 2000 >)

    Thx Hans and Macropod.

    Both your answers seem to point to the same thing. Hans, tx for the link. Jan Karel's code shows it quite nicely!
    I still need to test it in a workbook.
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is WorksheetFunction really necessary??? (Excel 2000 >)

    Macropod points out that an Excel worksheet function doesn't always work the same way as a VBA function with (almost) the same name. He takes DATEDIF / DateDiff as an example. Another one is ROUND / Round: in the worksheet function you can use a negative value for the decimal places argument: ROUND(235,-2) will result in 200, but the VBA function only works with non-negative values for decimal places.

    Jan Karel Pieterse mentions that there is a difference in the way VBA treats errors in Excel worksheet functions depending on whether you call them through the WorksheetFunction object or not. If you use the WorksheetFunction object, an error will cause the code to halt and display an error message, whereas if you omit WorksheetFunction, an error will cause the function to return an error value such as #N/A or #VALUE.

    Some worksheet functions aren't available in VBA through Application.WorksheetFunction.function or Application.function because they have direct VBA equivalents and hence are superfluous. Examples are TODAY (use Date instead) and string functions such as LEFT, MID and RIGHT (use Left, Mid and Right).

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is WorksheetFunction really necessary??? (Excel 2000 >)

    Thanks Hans. You really are thorough when you get down to explaining a concept. Thanx for that clarification. I understand now!
    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> to you all!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •