calling function in excel vba

[lezawang]

Hi
I noticed that i can not right sum() function alone like left(). what i mean by that, i have to use this syntax to call sun() function

x = Excel.WorksheetFunction.Sum(Range("a11:a20"))

however I can use left() function without using Excel.worksheetfucntion like the following

Cells(20, 3) = Left(Range("a1"), 3)

I tried to use sum() like the following

x = Sum(Range("a11:a20"))

but I got compile error msg - sub or function is not defined. Thanks

 

[StephenCrump]

Some functions, like LEFT() are both Excel functions and VBA functions.

Others, like Excel's SUM(), have no VBA equivalent, but can be called from VBA using WorksheetFunction.Sum or Application.Sum

If you look at the Members of the WorksheetFunction Object, you'll see that many, but not all, Excel functions can be called from VBA.
For example, you can't call Excel's MAX or MIN functions.

Also, be careful, there are differences in the way some functions behave between Excel and VBA. Even though VBA has a Round function, I will often use Application.Round so that my results round the same way that Excel does, rather than using VBA's "Banker's rounding".

 

[portews]

You have to use the WorksheetFunction or Application for functions that are not native to VBA. The SUM function is not native to VBA, therefore you have to tell VBA that that procedure is found in the application.

Here's a good description of Worksheet functions.
The WorksheetFunction Method – Daily Dose of Excel