XL97: Parentheses Around Object May Dereference the Object

function or procedure. When an object variable is enclosed in parentheses
				

   Worksheets.Add (Worksheets(1))
				

   Worksheets.Add Worksheets(1)
				

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.