XL97: Error When Variable Refers to Deleted or Moved Sheet

ID: Q158997

The information in this article applies to:

Microsoft Excel 97 for Windows

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.

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.

Additional query words: XL97 crash hang

Keywords : kberrmsg kbdta KbVBA xlvbmigrate
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug