Tip Printed from ExcelTip.com
Worksheet functions in VBA macros using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen 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. Examples:

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.