How to apply an array to a graph data series in excel using VB

user_15682200 0
Viewed 21

I am trying to create a number of small graphs on a single page and use data I have in an array. I have created 2 vectors as Series1 & Series2 (defined as Dim Series1(366) as double) and want to use as my data input. At the moment I write the data out to a worksheet and use the 'Range' (e.g."A3:A369") to create the series. The following code below works. I tried several alternatives with no success - examples

' With .SeriesCollection(1)'

' .Values = Series1

' End With

' ActiveChart.SeriesCollection(1).Add Source:=Series1

' ActiveChart.SeriesCollection.Add Source:=series2

 If iChartObject = 5 Then
    With ActiveSheet.ChartObjects.Add(Left:=1000, Width:=200, Top:=100, Height:=200)
        .Chart.ChartType = my_charttype
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = ChartTitle
    End With
End If
ActiveSheet.ChartObjects(iChartObject).Activate
ActiveChart.ChartArea.Select
str1 = Trim(str(lg_start))
str2 = Trim(str(lg_end))
str3 = "B" + str1 + ":B" + str2
str4 = ActiveSheet.Name
str5 = ActiveChart.Name
ActiveChart.SeriesCollection.Add Source:=ActiveSheet.Range(str3)
str3 = "C" + str1 + ":C" + str2
ActiveChart.SeriesCollection.Add Source:=ActiveSheet.Range(str3)

Any help appreciated

series graph excel vb.net arrays
1 Answers

I solved this problem (almost) using the code below . However I am now left with ZEROs causing the following. enter image description here I just want to see the area of the plot - i.e. no zeros. Series 1 &2 are defined as 'double'

For Each ch In ActiveSheet.ChartObjects
            ch.Chart.SeriesCollection.NewSeries
            ch.Chart.SeriesCollection(1).Values = Series1
            ch.Chart.SeriesCollection.NewSeries
            ch.Chart.SeriesCollection(2).Values = Series2
    Next
user_156822000
Related