The information in this article applies to:
SYMPTOMSIn 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:
-or-
CAUSEYou may receive one of these error messages if you enclose an object variable with parentheses when passing the object variable to another
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.
This behavior is by design of Microsoft Excel. 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.
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:
MORE INFORMATIONThis section describes several scenarios where your macro may fail or behave unexpectedly due to dereferencing of an object variable. Example 1
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
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 3The example below simply demonstrates how you can visualize the difference between an object that is dereferenced and one that is not.
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."
Q120802 Office: How to Add/Remove a Single Office Program or Component Additional query words: XL97 8.00
Keywords : kberrmsg kbprg kbdta kbdtacode KbVBA xlvbmigrate |
Last Reviewed: July 6, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |