XL97: Parentheses Around Object May Dereference the Object

Last reviewed: February 27, 1998
Article ID: Q159845

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, you may receive a run-time error or unexpected results from your macro if you enclose the argument for a procedure in parentheses when a value is not returned from the procedure. You may receive one of the following error messages:

   Run-time error '438':
   Object doesn't support this property or method

   -or-

   Run-time error '424':
   Object Required

CAUSE

You may receive one of these error messages if you enclose an object variable with parentheses when passing the object variable to another function or procedure. When an object variable is enclosed in parentheses and a return value is not expected, the object variable is "dereferenced." In other words, the Value property for the object is passed to the procedure instead of the object itself. This can produce either a run-time error or unexpected results.

RESOLUTION

To correct this problem, do not use parentheses around object variables that are passed to a function or procedure when a return value is not expected. Parentheses should only be used around object variables when a return value is needed.

For example, this line produces an error in Microsoft Excel 97 because the Worksheet object is dereferenced.

   Worksheets.Add (Worksheets(1))

Since parentheses are used around the argument, it is dereferenced; the Value property of the Worksheet object is passed to the Add method rather than the Worksheet object itself. The following line does not generate an error since the argument is not enclosed in parentheses and, thus, the Worksheet object is not dereferenced:

   Worksheets.Add Worksheets(1)

STATUS

This is by design in Microsoft Excel 97.

MORE INFORMATION

This section describes several scenarios where your macro may fail or behave unexpectedly due to dereferencing of an object variable.

Example 1

   Sub AddWorksheet()
       Worksheets.Add (Worksheets(1))  ' -- This line generates error
   End Sub

When this macro is run, the run-time error '438' is generated. When Microsoft Excel attempts to dereference "Worksheets(1)", a macro error occurs because the Worksheet object does not support the Value property.

Example 2

   Sub Main()
       GetRangeValue (Range("Sheet1!A1"))
   End Sub

   Sub GetRangeValue (x)
       MsgBox x.Value   ' -- This line generates error
   End Sub

When this macro is run, the run-time error '424' is generated. Microsoft Excel successfully dereferences the Range object for "Sheet1!A1" and passes the Value property of that Range object to the GetRangeValue procedure. The variable that is passed to GetRangeValue is not an object variable; instead, it could be a string or a double depending on the contents of the cell Sheet1!A1. The MsgBox line then fails because "x" is not an object variable.

Example 3

The example below simply demonstrates how you can visualize the difference between an object that is dereferenced and one that is not.

   Sub Test()
       MsgBox TypeName(Range("A1"))   ' -- NOT Dereferenced
       MsgBox TypeName((Range("A1"))) ' -- Dereferenced
   End Sub

When you run this macro, the first MsgBox returns "Range" as the type of the variable and the second MsgBox returns either "Double" or "String" depending on the contents of cell A1 in the active worksheet.

REFERENCES

For more information about using parentheses in your macros, click the Office Assistant in the Visual Basic Editor, type " parentheses," click Search, and then click to view "Use Parentheses in Code."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office Program or
               Component

Additional query words: XL97 8.00
Keywords : kbcode kberrmsg kbprg xlvbainfo xl97vbmigrate
Version : WINDOWS:97
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 27, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.