| I'm having trouble with ChartObjects: I'm potentially placing patches on patches while missing something fundamental. I'm using VBA in excel:mac 2011. I have 4 ChartObjects on a sheet, taking values from different data ranges and displaying them identically. They are simple values-over-time x-y line charts. The first takes its data from columns A:B on one sheet, the other three from columns A:B, C:D and E:F on another. They all run through the same code, with a Select Case statement to take the relevant data, then (for all) add the ChartObject and format it. The first two behave fine; the second two are causing problems. I set about trying to extract (what I think) are the key bits of the code, but it's probably easier to reproduce most of it (just extracting the maths for sizing and positioning) Sub s_CreateChartsTotals(i_call As Byte, i_lasttotalsrow As Integer, i_itemrows As Byte) ' Creates the charts on the front sheet ' Call 1 is the total range, call 2 is the last 5 years, call 3 the last 3 years, call 4 the last year Dim v_chart As ChartObject Dim v_datarange As Range Dim v_height As Integer Dim v_offsetleft As Integer Dim v_offsettop As Integer Dim v_xvalues As Range Dim y_values As Range ' Set the data to be used for each of the charts ' For some reason, the values taken for the x and y axis from other than the first two columns of the Totals for charts sheet ' need specifically assigning to those axes. Select Case i_call Case 1 ' Full chart: take all values from the Totals sheet, row 3 onwards Set v_datarange = Worksheets("Totals").Range("A3").Resize(i_lasttotalsrow - 2, 2) Case 2 ' Last 5 years: take values from columns A:B on Totals for charts sheet Set v_datarange = Worksheets("Totals for charts").Range("A:B") Case 3 ' Last 3 years: take values from columns C:D on Totals for charts sheet Set v_datarange = Worksheets("Totals for charts").Range("C:D") Set v_xvalues = Worksheets("Totals for charts").Range("C:C") Set v_yvalues = Worksheets("Totals for charts").Range("D:D") Case 4 ' Last 12 months: take values from columns E:F on Totals for charts sheet Set v_datarange = Worksheets("Totals for charts").Range("E:F") Set v_xvalues = Worksheets("Totals for charts").Range("E:E") Set v_yvalues = Worksheets("Totals for charts").Range("F:F") End Select ' Add the chart for this iteration ' *** stuff to calculate positioning variables removed *** Set v_chart = Worksheets("Total").ChartObjects.Add(v_offsetleft, v_offsettop, 192, v_height) v_chart.Activate ' Set the data as the pre-prepared datarange and format the line and axes, With ActiveChart .SetSourceData Source:=v_datarange, PlotBy:=xlColumns If i_call >= 3 Then .SeriesCollection(1).Values = v_yvalues .SeriesCollection(1).XValues = v_xvalues .SeriesCollection(2).Delete End If .PlotArea.Interior.ColorIndex = 36 .Legend.Delete .Type = xlLine With .SeriesCollection(1) .MarkerStyle = xlNone .Border.Weight = xlMedium End With ' .SeriesCollection(1) (i.e. the line) With .Axes(xlCategory).TickLabels .NumberFormat = "mm/yy" .Orientation = 90 End With ' .Axes(xlCategory).TickLabels .Axes(xlValue).TickLabels.NumberFormat = "$#,##0" End With ' v_chart End Sub As the comments in the code show, there's already a patch in there specifically to set the x and y datasources for the two charts taking from other than columns A and B on the sheet and to delete a spurious second data series that the default .Add creates. This works. However, when you save and reopen the file, these two ChartObjects have 'forgtten' part of this configuration (the x axis values are lost and a vertical line is drawn up the y axis). If you re-run the code that creates them as part of Worksheet.Open, all is displayed perfectly. However, this also involves doing a lot of other stuff which I don't want to do every time the worksheet is opened. Before I head off and re-engineer to isolate this specific code so that just the two problem ChartObjects are redrawn on opening, I think I need to get to the bottom of what the problem is. The root could be in how I'm taking from columns C:D and E:F rather than A:B in the first place, why I'm upsetting it, and / or how I declare this stuff such that it survives save and re-open of the file. Sorry if this 'goes on' a bit or is REALLY basic (I don't use charts much) but hopefully I've described the problem. Thank you. |
<tbody>
</tbody>
