Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Chart.SeriesCollection.NewSeries.Values
I loop through the following code to populate a 1D array (Series4Array) from
one of two different 2D arrays (HistDB8 or HistDB9). If ChartToggle = True Then ReDim Series4Array(UBound(HistDB8, 1) - 1) 'total percent For ii& = LBound(HistDB8, 1) + 1 To UBound(HistDB8, 1) For jj& = LBound(HistDB8, 2) + 1 To UBound(HistDB8, 2) If ThisDG = HistDB8(1, jj&) Then Series4Array(ii& - 1) = Round(HistDB8(ii&, jj&), 4) End If Next jj& Next ii& Else ReDim Series4Array(UBound(HistDB9, 1) - 1) 'total cost For ii& = LBound(HistDB9, 1) + 1 To UBound(HistDB9, 1) For jj& = LBound(HistDB9, 2) + 1 To UBound(HistDB9, 2) If ThisDG = HistDB9(1, jj&) Then Series4Array(ii& - 1) = HistDB9(ii&, jj&) End If Next jj& Next ii& End If I then use the 1D array (and three others) to initialize values in separate chart SeriesCollections. I loop through the following code to create one total percent chart and one total cost chart for a few dozen configurations. With ActiveSheet.ChartObjects.Add _ (Left:=MonChPosL, Width:=MonChPosW, Top:=MonChPosT, Height:=MonChPosH) .Chart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes" With .Chart.SeriesCollection.NewSeries .Values = Series1Array .XValues = SeriesXValuesArray .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series2Array .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series3Array .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series4Array .ChartType = xlLineMarkers .AxisGroup = 2 End With .... .... .... End With My problem is the values of the fourth SeriesCollection is always a set of zeros when Series4Array is taken from HistDB9 but not when its taken from HistDB8. The strange thing is I get no runtime errors. I've embedded a msgbox and confirmed that Series4Array is good. With .Chart.SeriesCollection.NewSeries If ChartToggle = False Then For ii& = LBound(Series4Array) To UBound(Series4Array) MsgBox "DG = " & ThisDG & "; Series4Array(" & ii& & ") = " & Series4Array(ii&) Next ii& End If .Values = Series4Array .ChartType = xlLineMarkers .AxisGroup = 2 End With Any help will be greatly appreciated. Thanks much. Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Chart.SeriesCollection.NewSeries.Values
Problem solved!
I changed the ChartToggle switch around to see if the problem was with the source data; changed the first line below to: If ChartToggle = False Then The result was that the percent data presented in the cost chart. But still no cost data in the other chart. So I went to the source of the cost data (an Access table) and changed the data type from currency to Number (Long Integer). And voila! So it goes. Kevin I wrote: I loop through the following code to populate a 1D array (Series4Array) from one of two different 2D arrays (HistDB8 or HistDB9). If ChartToggle = True Then ReDim Series4Array(UBound(HistDB8, 1) - 1) 'total percent For ii& = LBound(HistDB8, 1) + 1 To UBound(HistDB8, 1) For jj& = LBound(HistDB8, 2) + 1 To UBound(HistDB8, 2) If ThisDG = HistDB8(1, jj&) Then Series4Array(ii& - 1) = Round(HistDB8(ii&, jj&), 4) End If Next jj& Next ii& Else ReDim Series4Array(UBound(HistDB9, 1) - 1) 'total cost For ii& = LBound(HistDB9, 1) + 1 To UBound(HistDB9, 1) For jj& = LBound(HistDB9, 2) + 1 To UBound(HistDB9, 2) If ThisDG = HistDB9(1, jj&) Then Series4Array(ii& - 1) = HistDB9(ii&, jj&) End If Next jj& Next ii& End If I then use the 1D array (and three others) to initialize values in separate chart SeriesCollections. I loop through the following code to create one total percent chart and one total cost chart for a few dozen configurations. With ActiveSheet.ChartObjects.Add _ (Left:=MonChPosL, Width:=MonChPosW, Top:=MonChPosT, Height:=MonChPosH) .Chart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes" With .Chart.SeriesCollection.NewSeries .Values = Series1Array .XValues = SeriesXValuesArray .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series2Array .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series3Array .ChartType = xlColumnStacked End With With .Chart.SeriesCollection.NewSeries .Values = Series4Array .ChartType = xlLineMarkers .AxisGroup = 2 End With ... ... ... End With My problem is the values of the fourth SeriesCollection is always a set of zeros when Series4Array is taken from HistDB9 but not when its taken from HistDB8. The strange thing is I get no runtime errors. I've embedded a msgbox and confirmed that Series4Array is good. With .Chart.SeriesCollection.NewSeries If ChartToggle = False Then For ii& = LBound(Series4Array) To UBound(Series4Array) MsgBox "DG = " & ThisDG & "; Series4Array(" & ii& & ") = " & Series4Array(ii&) Next ii& End If .Values = Series4Array .ChartType = xlLineMarkers .AxisGroup = 2 End With Any help will be greatly appreciated. Thanks much. Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning array values to seriescollection | Excel Programming | |||
SeriesCollection Values Property Oddity | Charts and Charting in Excel | |||
Cannot change SeriesCollection.Values! Why not? | Excel Programming | |||
Line colour of .SeriesCollection.NewSeries? | Excel Programming | |||
Strange behavior Chart.SeriesCollection(n).Values property | Excel Programming |