Microsoft KB Archive/158997

= XL97: Error When Variable Refers to Deleted or Moved Sheet =

Article ID: 158997

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q158997





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



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 kbbug KB158997

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.