» 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
CATEGORY - Custom Functions , Printing in VBA
VERSION - All Microsoft Excel Versions
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.
Book Store:
Functions tha doesn´t work
Leo Posted on: 31-12-1969
I´ve tried to use some date functions, such as networkdays, date and year, on the vba enviroment but i had no success, do you have any tip for this kind of functions.Using functions from the ATP addin
Ole P. Erlandsen Posted on: 31-12-1969
If you want to use functions from the Analysis Tool Pack addin in your own macros:
- Open the VBE (Alt+F11).
- Activate the project where you want to use the function(s).
- Select Tools, References... and check the option atpvbaen.xls.
- click the OK-button to close the References-dialog.
The macros in the workbook where you added the reference to the atpvbaen.xls library can now use the functions like this:
workdaycount = networkdays(Date, Date + 14)
Or like this to avoid conflict with other user defined functions with the same name:
workdaycount = [atpvbaen.xls].networkdays(Date, Date + 14)
It is not necessary to install the Analysis Tool Pack addin from the menu Tools, Add-Ins...
The DATE() and YEAR() worksheet functions are not necessary in VBA where you use the built-in functions Date and Year:
Today = Date
Tomorrow = Date + 1
CurrentYear = Year(Date)
NextYear = Year(Date) + 1
Comma or semicolon
savas aslanaga Posted on: 31-12-1969
when I use worksheet functions, arguments in functions are separeted by semicolon otherwise they dont work . for instance if I separate them with comma.
i.e.
OFFSET(Sheet1!$E$8,COUNT(Sheet1!$E:$E)-1,0) dosent work.
OFFSET(Sheet1!$E$8;COUNT(Sheet1!$E:$E)-1;0) works
Reply: Comma or semicolon - savas aslanaga from Turkey wrote on August 14, 2003 1:41 AM EST
Alan Posted on: 31-12-1969
Hi Savas,
I think this is an OS setting - could be Excel in a version I am not familar with though.
What OS and version of Excel are you using?
Alan.


