Worksheet functions in VBA macros

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.

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...

Document last updated 2003-04-27 12:45:07 Printerfriendly version

User comments:

Ole P. from Norway wrote (2005-04-10 15:30:14 CET):

Re: Revised on previous question
One possible solution:
Add a new (temporary) column to your data matrix.
Enter the formula below and copy down as far as necessary:
=MOD(INT(A2*1440+0.5),60)
The formula assumes the the time value is in column A.
Now you can apply AutoFilter and set the filter criteria in your new column to show all rows containing a zero value (all hourly data rows).
Copy and paste to your new target range.

Mehran from Dubai wrote (2005-04-10 10:41:09 CET):

Revised on previous question
Hi there.
Let me clear my previous question.
Actually I have a matrix of data(10x1000),
which is included of meteorological data with 2 minutes interval.
How is possible to extract hourly data from this Excel sheet.
Regards,
Mehran

Mehran from Dubai-UAE wrote (2005-04-10 10:35:28 CET):

Need to create odd rows
Good day.
Is there any commands to craete the odd rows in the Excel sheet.

Regards,
Mehran

Ole P. from Norway wrote (2004-08-17 09:26:18 CET):

Re: IN EXCEL 2000
This will store the current row number in a variable:
r = ActiveCell.Row

This will store the value in J22 in a variable:
x = Range("J22").Value

Or if the active cell is in row 22:
x = Range("J" & ActiveCell.Row).Value

Jacques Fortin from E mail : paulette006@sympatico.ca wrote (2004-08-17 01:20:08 CET):

IN EXCEL 2000
good day
thank you for your help
NEED TO COPY THE ROW NUMBER IN VARIABLE, SAY THE ROW NUMBER IS 22
LINE INFORMATION IS
A-000020 M ROSS OLIVER BRAUN 000000048 000000000 23 SEPTEMBRE 1888 23/09/1888 TORONTO, CANADA A-000105 A-000473 2 0 1 F F F F
OR NEED TO COPY CELL "J" ON THE SAME ROW(22)
for macro in editor VBA