Microsoft Knowledge Base

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

SYMPTOMS

In Microsoft Excel, if you turn on the macro recorder, and you use the Chart Wizard to create a chart, and the source data for the chart is from a nonadjacent selection of cells, you will receive the following error message when you run the macro:

   Error in formula

CAUSE

This behavior occurs only when you record a macro using the MS Excel 4.0 macro language. The problem occurs because the complete reference is recorded only for the first selection of cells. The additional recorded references contain the cell reference only, without the workbook and worksheet.

For example, if you select the range A1:A3 and B2:B4 on Sheet1 in BOOK1.XLS to create the chart, the references are recorded as [BOOK1.XLS]!Sheet1.xls!R1C1:R3C1, and R2C3:R4C3.

Note that in Microsoft Excel versions 5.x and 7.0, this problem occurs when you use the Chart Wizard to create a chart on its own sheet, or an embedded chart on a worksheet other than the worksheet that contains the chart data.

WORKAROUNDS

To avoid receiving a macro error when you record a macro that creates a chart from nonadjacent cells, do either of the following:

• In Microsoft Excel versions 5.x and 7.0, record the macro in the Visual Basic macro language.

  -or-

• Edit the recorded MS Excel 4.0 macro and add the workbook and worksheet name to the cell references that are incomplete.