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. ***
Name = Chart_Name & " ***."

' 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"botha822" <[email protected]> wrote in
message news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> 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.
> ***
> Name = Chart_Name & " ***."
>
> ' 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
>
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile:
> http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=570330
>