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
jfz2004Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Sorry replace c with a

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

' Shorter
Res = WorksheetFunction.Average(a)
Res = WorksheetFunction.StDev(a)
0
 
Rgonzo1971Commented:
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
0
 
jfz2004Author Commented:
I will try. Thanks. but what is c in the functions?
0
 
Rory ArchibaldCommented:
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).
0
 
jfz2004Author Commented:
Let me try it with a brand new worksheet. I first try failed with this error:
sub or function not defined.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.