» 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.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
