» Worksheet functions in VBA macros using VBA in Microsoft Excel
VBA macro tip contributed by
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
Almost all of the built-in worksheet functions can be used in macros.
If you have a non-English version of Excel you have to remember to use the English function names,
and that the separator sign between the function arguments always is a comma (,).
If you don't know the English function name, record a macro while you enter the function the usual way in a cell,
in the registered macro the function name will be translated to English.
When you are going to use a worksheetfunction in a macro you also have to include the Application-object.
In Excel97 you use the object Application.WorksheetFunction.
MyTotal = Application.Sum(Range("A1:A100"))
' returns the total of the values in A1:A100 in the active worksheet.
MyTotal = Application.WorksheetFunction.Sum(Range("A1:A100"))
' the same formula for Excel 97.