XL97: Error When Variable Refers to Deleted or Moved Sheet (158997)

SYMPTOMS

When you run a Visual Basic for Applications macro in Microsoft Excel 97, you may receive one of the following error messages:

Run-time error '-2147221080 (800401a8)':
Automation error

-or-

Run-time error '-2147221080 (800401a8)':
Method 'Name' of object '_Worksheet' failed

-or-

Run-time error '424':
Object required

If you click Debug to check the value of a variable in the Immediate window, you may receive the following error message:

This program has performed an illegal operation and will be shut down.
If the problem persists, contact the program vendor.

If you click Details, you receive an error message similar to the following:

EXCEL caused an invalid page fault in module VBA332.DLL at
014f:651b5717.

and Microsoft Excel 97 stops responding.

CAUSE

This problem occurs when all of the following conditions are true:

In a Visual Basic macro, you create a variable of type Worksheet or Object that refers to a worksheet within a workbook.

-and-
The sheet to which the variable refers is deleted or moved to another workbook.

-and-
You attempt to check any property of the variable.

NOTE: If you click the Debug button in the error message dialog box, click the Immediate window in the Visual Basic Editor, and then attempt to use the variable in question, an invalid page fault occurs. Or, a page fault occurs when you position the pointer over any section of code that refers to the variable in question in the Code window.

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following macro moves Sheet1 from the active workbook into a new workbook:

   Sub Test()

       Dim xSheet As Worksheet, newWorkbook As Workbook
       Set xSheet = ThisWorkbook.Sheets("Sheet1")
       Set newWorkbook = Workbooks.Add
       xSheet.Move Before:=newWorkbook.Sheets(1)
       MsgBox xSheet.Name

   End Sub

If you attempt to check the value of xSheet after moving the sheet to the new workbook, the error appears. For example, the following line of code fails because xSheet refers to a worksheet that no longer exists in the same workbook:

   MsgBox xSheet.Name

To work around this problem, redefine the variable to point to the new location of the sheet. Or, set the value of the variable equal to Nothing. To do this, use either of the following methods:

Method 1: Set the Variable to Point to the New Location of the Worksheet

   'Because xSheet is now the first sheet in the new workbook,
   'redefine the variable appropriately.
   Set xSheet = newWorkbook.Sheets(1)

   'This line of code now works correctly.
   MsgBox xSheet.Name

Method 2: Set XSheet Equal to Nothing

   Set xSheet = Nothing

MORE INFORMATION

In earlier versions of Microsoft Excel, if you create a variable that refers to a sheet, and then move that sheet to another workbook, the variable continues to find to the sheet in its new location. For example if you run the following subroutine in Microsoft Excel 5.0 or 7.0, the MsgBox displays the name of the moved sheet ("Sheet1 (2)") even when you move the worksheet to a different workbook:

   Sub MoveSheet()

       Dim xSheet As Object, newWorkbook As Object
       Set xSheet = ThisWorkbook.Sheets(1)
       Set newWorkbook = Workbooks.Add
       xSheet.Move Before:=newWorkbook.Sheets(1)
       MsgBox xSheet.Name

   End Sub

In Microsoft Excel 97, this macro does not work correctly, because the variable xSheet cannot find the moved sheet in its new location. This change in behavior may cause problems if your code assumes that the variable continues to refer to the sheet even if it is moved. In this case, use the first method to redefine the variable to refer to the sheet in the new location.