Link to home
Start Free TrialLog in
Avatar of jfz2004
jfz2004

asked on

How to call Excel's function (mean, standard deviation) in VBA?

Hi,

I know how to write a function in VBA to calculate mean. But since I know Excel has this function already so I don't want to write it myself. Besides, using the Excel inbedded function maybe faster in calculation. So I want to call the function in VBA. How to do that?

Thanks for any help?

Jennifer
Avatar of Rgonzo1971
Rgonzo1971

HI,

pls try
a = array(3,4)
Res = Application.WorksheetFunction.Average(c)
Res = Application.WorksheetFunction.StDev(c)

' Shorter
Res = WorksheetFunction.Average(c)
Res = WorksheetFunction.StDev(c)

Open in new window

Regards
Avatar of jfz2004

ASKER

I will try. Thanks. but what is c in the functions?
Where VBA doesn't have an equivalent function built in, you can use either Application.WorksheetFunction.function_name or simply Application.function_name - for example:
Application.WorksheetFunction.Average(Range("A1:A10"))

Open in new window

or:
Application.Average(Range("A1:A10"))

Open in new window


The worksheetfunction version is typically a little faster if you are passing ranges rather than arrays to it, and the error handling is different. Application.WorksheetFunction.function_name raises a run-time error if it fails, which you have to handle with an On Error statement, whereas Application.function_name returns an error value that you can assign to a Variant variable and test using IsError(variable_name).
Avatar of jfz2004

ASKER

Let me try it with a brand new worksheet. I first try failed with this error:
sub or function not defined.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial