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
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
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.WorksheetFunct ion.functi on_name or simply Application.function_name - for example:
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.WorksheetFunct ion.functi on_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).
Application.WorksheetFunction.Average(Range("A1:A10"))
or:Application.Average(Range("A1:A10"))
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.WorksheetFunct
ASKER
Let me try it with a brand new worksheet. I first try failed with this error:
sub or function not defined.
sub or function not defined.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pls try
Open in new window
Regards