Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Not All Worksheet Functions Supported as Application
Article ID: 107564 - View products that this article applies to.
This article was previously published under Q107564
In Microsoft Visual Basic for Applications, not all Microsoft Excel worksheet functions are supported as methods of the Application object. If you attempt to use one of these worksheet functions with the Application object, you will receive the following error message:
Run-Time Error '438':
Object doesn't support this property or method
You can write Visual Basic procedures that call Microsoft Excel worksheet functions. The worksheet functions that are available to Visual Basic procedures are in the Application object, and are listed in the Methods/Properties list for the Application object in the Visual Basic Object Browser.
Most worksheet functions that are not available as methods of the Application object have an equivalent Microsoft Visual Basic built-in operator or function. For example, the Mod() worksheet function is not available as a method of the Application object because there is an equivalent Mod operator built-in to Visual Basic. This is by design, because a Visual Basic operator works faster than a Microsoft Excel function in a Visual Basic module.
Microsoft Excel 97 and Microsoft Excel 98NOTE: The following steps assume that you 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:
Microsoft Excel version 7.0NOTE: The following steps assume that you installed the Visual Basic Help file.
To display a list of all the Microsoft Excel worksheet functions available as methods of the Application object in Visual Basic, follow these steps:
Microsoft Excel version 5.xTo display a list of all the Microsoft Excel worksheet functions available as methods of the Application object in Visual Basic, follow these steps:
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.
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 functin 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 equivalant (none needed) TAN() Tan function TODAY() DATE function TRUNC() FIX function TYPE() TYPENAME function UPPER() UCASE function VALUE() VAL function
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)")
x = Application.Evaluate("Atan(12)")
Article ID: 107564 - Last Review: October 11, 2006 - Revision: 1.3