# Thread: Is WorksheetFunction really necessary??? (Excel 2000 >)

1. ## 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?

2. ## 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!

3. ## 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. ## 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.

5. ## 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. ## 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!

#### Posting Permissions

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