Not all Excel worksheet functions are supported as methods of the Application object in Visual Basic for Applications

Excel Worksheet Functions Available as Methods in Visual Basic

NOTE: The following steps assume that you have installed the Visual Basic Help file.


To display a list of all the Microsoft Excel worksheet functions that are available as methods of the Application object in Visual Basic, follow these steps:

1. In the Visual Basic Editor, type worksheet functions in the Microsoft Office Assistant, and then click Search.
   

2. In the list of topics, click List of Worksheet Functions Available to Visual Basic.
   

To locate the equivalent Visual Basic operator or function of a Microsoft Excel worksheet function that is not available as an Application object method, follow these steps:

1. In the Visual Basic Editor, type the function or operator name in the Office Assistant, and then click Search. For example, type mod.
   

2. In the list of topics, click the topic for the function or operator. For example, click the Mod Operator topic.
   

The following table contains worksheet functions that are not available as methods of the Application object. The Visual Basic Equivalent column lists functions you can use that produce results the same as (or similar to) results that the functions return.


Microsoft Excel Visual Basic
worksheet function equivalent
-------------------------------------------------------------------
ABS() Abs function
ATAN() Atn function
CHAR() CHR function
CODE() ASC function
COS() Cos function
DATE() DATESERIAL function
ERROR.TYPE() ERR function
EXACT() STRCOMP function
EXP() Exp function
INDIRECT() No direct equivalent - use Range,
Cells, Offset, or any other way of
referencing cells.
INFO() No direct equivalent - use the
following:
CURDIR function, the following
Application properties (MEMORYTOTAL,
MEMORYUSED, MEMORYFREE, VERSION,
OPERATINGSYSTEM, CALCULATION, COUNT)
ISBLANK() ISEMPTY function
ISREF() TYPENAME function
LEN() LEN function
LOWER() LCASE function
N() No equivalent (none needed)
NA() No equivalent - use
ActiveCell.Value = "#N/A"
RAND() RND function
RANDOMIZE() Randomize function
SIN() Sin function
SIGN() Sgn function
SQR() Sqr function
SQRT() Sqr function
T() No equivalent (none needed)
TAN() Tan function
TODAY() DATE function
TRUNC() FIX function
TYPE() TYPENAME function
UPPER() UCASE function
VALUE() VAL function
NOTE: The Microsoft Excel worksheet function and the Visual Basic equivalent operator or function are not always calculated the same way and may give different results, even when they have the same name.


Using the Visual Basic equivalent operator or function is preferred for increased speed and flexibility in calculations. Additionally, the Visual Basic operator or function is available to all applications that use Microsoft Visual Basic for Applications.


Although it is recommended that you use the Visual Basic operators or functions shown in the list above, there are alternative methods for using the worksheet functions in a Visual Basic macro if necessary. As an example, you can use the ATAN worksheet in your Visual Basic macro using the ExecuteExcel4Macro or Evaluate functions:


x = Application.ExecuteExcel4Macro("Atan(12)")
-or-


x = Application.Evaluate("Atan(12)")