Microsoft Knowledge Base

The information in this article applies to:

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In the versions of Microsoft Excel mentioned above, if you open a Microsoft Excel version 4.0 workbook file, and run a macro contained in the workbook file that references the workbook name, you receive a macro error message.

CAUSE

You receive this error message when a command in the Microsoft Excel version 4.0 macro references a sheet in the workbook, or the workbook itself is using the workbook name listed on the Window menu.

In the versions of Microsoft Excel mentioned above, when you open a Microsoft Excel version 4.0 workbook file that was saved while the bound sheets were open, a separate window is opened for each sheet in the workbook. The workbook name appears on the Window menu for each open sheet, with the workbook name followed by a colon and a number indicating the window number.

For example, when you open the Microsoft Excel version 4.0 workbook file, BOOK1.XLS that contains the sheets SHEET1.XLS and SHEET2.XLS, the Window menu displays BOOK1.XLS:1 (with SHEET1.XLS as the active sheet), and BOOK1.XLS:2 (with SHEET2.XLS as the active sheet). If your macro uses the workbook name listed on the Window menu, for example, if you use the UNHIDE macro formula to open an embedded chart in a workbook, and use the WINDOWS macro function to get the workbook name, the macro error occurs because of the number following the workbook name.

WORKAROUND

To avoid receiving a macro error when you run a macro that references the name of a Microsoft Excel version 4.0 workbook from the Window menu, do either of the following:

• Close all of the workbook windows except for one, so that the window name for the workbook does not contain a number. You can either do this manually before you run the macro, or you can do this from within the macro.

  -or-

• You can test the window name to see if a colon ":" exists in the workbook name, and then truncate the last two characters in the window name by using the following macro as an example:

      A1: =SET.NAME("WinName",INDEX(WINDOWS(),1))
      A2: =IF(NOT(ISERR(FIND(":",WinName))))
      A3: =SET.NAME("WinName",MID(WinName,1,LEN(WinName)-2))
      A4: =END.IF()
      A5: =UNHIDE(WinName & " Chart 1")
      A6: =RETURN()