problem with dynamic data in chart?

[botha822]

--------------------------------------------------------------------------------

I have been having an intermittent problem with creating a chart and
adding a new series. Sometimes the sub runs smoothly and adds the
chart. Othertimes it produces an error:
"Runtime error 1004: Unable to set the Values Property of the Series
Class"

The Values property is referenced to a range that is updated
periodically based on info from a Bloomberg API call. I suspect that
for some reason excel believes that the range is empty and therefore
cannot assign it to the value property. I have tried a wait timer as
well as disabling screenupdate and turning calculations to manual, none
of which have solved the problem. Here is the source

Sub chart_from_scratch(Chart_Name As String, WkS_Name As String)

Dim Xvalue As String
Dim Value As String
Dim Name As String

Dim co As ChartObject
Dim lastrow As Long

lastrow = LastCellBeforeBlankInColumn()

Xvalue = "=" & WkS_Name & "!R5C2:R" & lastrow & "C2" 'ref to dates
Value = "=" & WkS_Name & "!R5C9:R" & lastrow & "C9" 'ref. to the Sys.
Cum
Name = Chart_Name & " Cum."

' prevent chart series error
If Selection.Parent.Type = 4 Then
' if chart element is selected, we'll be hosed later
ActiveWindow.Visible = False
End If


Set co = Worksheets(WkS_Name).ChartObjects.Add(500, 300, 400, 200)

'ActiveChart.Location Where:=xlLocationAsObject, Name:=Chart_Name
'Call Wait(4)
With co.Chart
..ChartType = xlArea
..SeriesCollection.NewSeries
..SeriesCollection(1).XValues = Xvalue
..SeriesCollection(1).Values = Value
..SeriesCollection(1).Name = Chart_Name
End With

'co.Name = Name
Application.Run "BLPLinkReset"
' Offset location
'ActiveSheet.Shapes(Name).IncrementLeft 160
'ActiveSheet.Shapes(Name).IncrementTop 115

co.Chart.ChartType = xlLine
With co.Chart.Axes(xlCategory).Border
..Weight = xlHairline
..LineStyle = xlAutomatic
End With
With co.Chart.Axes(xlCategory)
..MajorTickMark = xlOutside
..MinorTickMark = xlNone
..TickLabelPosition = xlLow
End With
With co.Chart.Axes(xlCategory).TickLabels
..Alignment = xlCenter
..Offset = 100
..ReadingOrder = xlContext
..Orientation = 45
End With
End Sub

 

[Jon Peltier]

You get an error referencing the Values even for an Area chart? That's
usually the suggestion to avoid this problem, at least if a range doesn't
contain plottable data.

What's the sheet name? Does it contain any spaces? If so, it must be
enclosed in single quotes:

Value = "='" & WkS_Name & "'!R5C9:R" & lastrow & "C9"

- Jon