XL: Not All Worksheet Functions Supported as Application

ID: Q107564

The information in this article applies to:

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/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 98

NOTE: 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:

1. In the Visual Basic Editor, type "worksheet functions" (without the 

   quotation marks) 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"
   (without the quotation marks).
2. In the list of topics, click the topic for the function or operator. For 
   example, click the "Mod Operator" topic.

Microsoft Excel version 7.0

NOTE: 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:

1. Click the Index tab in Microsoft Excel Help, type "worksheet 

   functions, using" (without the quotation marks), and then click Display.
2. In the list of topics, click "List of Worksheet Functions Available to 
   Visual Basic," and then click Display.
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. Click the Contents tab in Microsoft Excel Help.

2. In the list of topics, click Microsoft Excel Visual Basic Reference, and 

   click Open. Click Functions, and then click Open.
3. Click the letter of the operator or function that you want to use, and 
   click Open.
4. Click the operator or function with the same name as the function you 
   want to use, and click Display.

Microsoft Excel version 5.x

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

1. Click the Search button in Visual Basic Reference Help, type "worksheet 

   functions" (without the quotation marks), and then click Show Topics.
2. In the list of topics, click "Using Worksheet Functions in Visual 
   Basic," and then click Go To.
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. Click the Search button in Visual Basic Online Help and type: 

      programming language
2. Click Show Topics, and choose Go To.

3. Click the operator or function with the same name as the function you 

   want to use.
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 will produce the same (or similar) 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 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
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: 5.00 5.00c 7.00 8.00 application.worksheet application.function XL97 XL98 XL7 XL5 
Keywords          : kbprg kbdta PgmHowto KbVBA 
Version           : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a; winnt:5.0
Platform          : MACINTOSH WINDOWS winnt
Issue type        : kbinfo

Last Reviewed: March 7, 1999