problem with dynamic data in chart?
 

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

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 Whe=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

problem with dynamic data in chart?
 

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" wrote in
message ...

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

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 Whe=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