[TABLE="width: 1128"]
<tbody>[TR]
[TD]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.
[/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD]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.
[/TD]
[/TR]
</tbody>[/TABLE]
