Member Avatar for Tekito

This is an issue that I've discusses many times before, but never with a conclusive solution. I am trying to programmatically change the data range for an existing chart in Excel. My code goes something like this:

With WS.CharObjects(SelChart).Chart
  With .SeriesCollection(1)
    .XValues =DataWS.Range("A1:A101")   '<-----ERROR!
    .Values = DataWS.Range("B1:B101")
  End With
End With

The .XValues line results in the "1004 Unable to set the XValues property of the Series class" error. The range contains valid data: numbers, no blanks. When I manually set the chart to this range, everything is fine. Like I said, I've read many people having this problem, but nothing is working for me. I've had no end of trouble dealing with Excel charts. Sometimes they work, sometimes they don't.

  • 4 Contributors
  • 6 Replies
  • 281 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by gmcfarlane

Dani AI

Generated

A short, practical summary and next steps for Err#1004 when assigning Series.XValues (context: has the chart and data on different sheets; thread replies noted range-qualification, string-workarounds and a duplicate-label problem from a shifted recordset).

Common checks to run first: confirm the Range object is a Worksheet-qualified Range from the same open workbook as the chart (unqualified Ranges or ranges in a closed workbook often fail); confirm X and Y point counts match exactly; verify there are no error/blank cells in the X range; check chart type (XY/scatter expects numeric Xs while category charts accept labels); and reproduce the same range assignment manually to compare the series formula before/after. ’s case shows duplicate or misaligned values in the X column can reproduce the error, so confirm the X column actually contains the intended unique labels or numbers.

Two robust programmatic approaches that often bypass the intermittent error:

  • Assign a true 1-D Variant array to XValues (avoid passing a 2-D range array directly). Example:
Dim arrX As Variant
arrX = Application.Transpose(wsData.Range("A1:A101").Value)
chtObj.Chart.SeriesCollection(1).XValues = arrX
  • Set the series formula explicitly (full external addresses avoid ambiguity):
Dim f As String
f = "=SERIES(" & wsData.Range("C1").Address(External:=True) & "," & _
    wsData.Range("A1:A101").Address(External:=True) & "," & _
    wsData.Range("B1:B101").Address(External:=True) & ",1)"
chtObj.Chart.SeriesCollection(1).Formula = f

If assignment still fails, recreate the series (delete then NewSeries) so Excel reinterprets the sources, or copy the source block next to the chart and set source data there as a test. Caveats: Transpose can be unreliable for very large ranges and XY charts require numeric Xs. These steps combine ideas raised in the thread (range qualification, string/array workarounds, and checking for shifted/duplicate values) into concrete, safe operations that tend to resolve the Err#1004 symptom.

Recommended Answers

All 6 Replies

Member Avatar for QVeen72

Hi,

I Guess DataWS is not set to the referring Worksheet...

Try this :

With WS.CharObjects(SelChart).Chart
  With .SeriesCollection(1)
    .XValues =Sheets("Sheet1").Range("A1:A101")   
    .Values = Sheets("Sheet1").Range("B1:B101")
  End With
End With

Regards
Veena

Member Avatar for Tekito

The data columns and graph are on two different worksheets. I have tried explicitly naming the worksheets (which shouldn't make any difference) and other trivial syntax variations, but nothing works.

Member Avatar for QVeen72

Hi,

Well.. I Guess, after Googling with the error said, it appears that, it is a BUG in Excell 2k. Not sure, if the higher versions of Excel have been cleared of that bug..

Regards
Veena

Member Avatar for Tekito

Yeah, so it appears... I've pretty much resigned to finding workarounds. I really am not very fond of Excel charts.

Member Avatar for shikeh

Hi
I found that this bug cause the program not working only when you do a presentation in front of managers. then it crash :(
but my solution for this is to run-over all the range (or array, in my case) and make it a string
this is the code for the string (pointing every time on Reported (N):

ReportedString = ReportedString & Chr(&H22) & Reported(N) & Chr(&H22) & ","

we need this Chr function because we need to insert string as the Xvalue (and we can just like that add ' " ')
after that, remove the last "," by this code:

ReportedString = Left(ReportedString, Len(ReportedString) - 1)

then, the last one is to add the brackets:

ReportedString = "={" & ReportedString & "}"

after this, just:

.XValues= ReportedString

Hope it will help you (it helpd me :) )
Shikeh

Member Avatar for gmcfarlane

HI

I am reworking a spreadsheet someone else wrote, sigh....
I was getting this error pretty consistently trying to assing XValues

When i went back to the original, i realized my recordset was offset by 1 column
The XValues are defined in 2 columns, year quarters in B and groups in C.

What seems to cause the problem is that when i shifter the result set left , the column which should have been populated w/ unique values now has duplicates in it.

I can reproduce or eliminate the error at will by pointing at the correct or incorrect columns.

Dont know if this applies to everyone in this thread, but this was the issue for me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.