ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address