Microsoft KB Archive/213660

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

Article ID: 213660

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q213660



For a Microsoft Excel 97 and earlier or Microsoft Excel 98 Macintosh Edition or earlier version of this article, see 107564.



SUMMARY
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



MORE INFORMATION
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 or 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. A Visual Basic operator works faster than a Microsoft Excel function in a Visual Basic module.

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)")

Additional query words: application.worksheet application.function XL2000 Run-Time Error 438 Object doesn t support this property or method XL2007 XL2003

Keywords: kberrmsg kbhowto kbprogramming KB213660

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.