Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Why doesn't my chart update when underlying data changes? | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |