XL: "Invalid External Reference" Using ChartWizard MethodID: Q126817
|
In Microsoft Excel, you may receive the following error message when
you run a Visual Basic, Applications Edition, procedure that contains the
ChartWizard method:
Run-time error '1004':
External reference is not valid
This problem occurs if the Source argument that you use with the
ChartWizard method contains a number of nonadjacent range references.
Note that the number of the range references that will cause this
error message depends on the length of the name of the worksheet that
the chart is created on.
For example, the following procedure correctly creates a chart on the
active sheet, Sheet1, (assuming that the range A1:B14 on Sheet1
contains data):
Sub CreateChart()
ActiveSheet.ChartObjects.Add(100, 40, 300, 200).Select
Application.CutCopyMode = False
ActiveChart.ChartWizard Source:= Range( _
"A1:B1,A2:B2,A3:B3,A4:B4,A5:B5,A6:B6,A7:B7," _
& "A8:B8,A9:B9,A10:B10,A11:B11,A12:B12,A13:B13")
End Sub
If you add another reference in the Range object for the Source argument,
you receive the error message described above when you run the procedure.
To avoid receiving the error message above, use one of the following
methods.
Method 1: Use the MS Excel 4.0 Macro equivalent, CHART.WIZARD().
The CHART.WIZARD() function works correctly with nonadjacent cell references because the range remains a string and does not need to be converted. The original string that you use as the Ref argument in the CHART.WIZARD function is the string that is parsed.Method 2: Copy and paste the cells in the range from which you are creating the chart so that they appear as one contiguous range. This range can then be referred to as one single range in the ChartWizard method.
Microsoft has confirmed this to be a problem in the Microsoft Excel versions listed above. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
The ChartWizard method modifies the properties of the given chart and
allows a chart to be quickly formatted without setting all the
individual properties. This method is non-interactive and changes
only the specified properties.
One of the named arguments for the ChartWizard method is called Source. The
Source argument is used to specify the range that contains the source data
for the chart. It is required if you are creating a chart and is optional
if you are editing an existing chart.
If Source is omitted and the selection is not an embedded chart on the
active worksheet, or the active sheet is not an existing chart, the method
fails and an error occurs.
For more information about the ChartWizard Method, choose the Search
button in Help and type:
ChartWizard Method
Additional query words: 5.00c
Keywords : kbcode
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform :
Issue type :
Last Reviewed: July 29, 1999