XL2000: Error Opening Workbook That Contains Macro (199212)

SYMPTOMS

When you open a workbook and click either Disable Macros or Enable Macros, Excel stops responding and you may receive one of the following error messages:

In Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows Millennium Edition (Me)

This program has performed an illegal operation and will be shut down.

When you click Details, you receive the following message:

EXCEL caused an invalid page fault in module EXCEL.EXE at 015f:300fbe4a.

When you click Close, the program shuts down.

In Microsoft Windows NT 4.0

An application error has occurred and an application error log is being generated. excel.exe Exception: access violation (0xc0000005), Address: 0x3046ff43

In Microsoft Windows 2000 and Microsoft Windows XP

Excel quits, but you receive no error message.

CAUSE

This problem occurs when you do the following:

You run a Visual Basic for Applications macro that deletes one or more worksheets and saves the workbook.

-and-
The macro refers to the worksheet directly by its object name as opposed to referring to it as part of a collection. For example, using the syntax Sheet1.Delete instead of Sheets(1).Delete.

-and-
You open the workbook that has been saved using the macro.

WORKAROUND

To avoid this problem, do not refer to the worksheet by its object name. Instead, use the index or worksheet name within the Sheet collection. For example, the following macro will not cause the invalid page fault error when you reopen this workbook. (This example requires the ActiveWorkbook contains more than two worksheets and one sheet is named Sheet1.):

   Sub SaveBook()
      Sheets("Sheet1").Delete
      Sheets(2).Delete
      ActiveWorkbook.SaveAs filename:="C:\Mybook.xls"
   End Sub

REFERENCES

For more information about Sheets collection, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type sheets property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.