Creating VBA Chart using Array

Question

I am trying to create a excel chart using vb6. Instead of feeding a excel range im trying to feed an array. And im getting an error. This is the code that im working on

Private Sub CreateChart(Optional ByVal ChartTitle As String _
                , Optional ByVal xAxis As Excel.Range _
                , Optional ByVal yAxis As Excel.Range _
                , Optional ByVal ColumnName As String _
                , Optional ByVal LegendPosition As XlLegendPosition = xlLegendPositionRight _
                , Optional ByVal rowIndex As Long = 2 _
                , Optional ByRef ChartType As String = xlLineMarkers _
                , Optional ByVal PlotAreaColorIndex As Long = 2 _
                , Optional ByVal isSetLegend As Boolean = False _
                , Optional ByVal isSetLegendStyle As Boolean = False _
                , Optional ByVal LegendStyleValue As Long = 1)

Const constChartLeft = 64
Const constChartHeight = 300
Const constChartWidth = 700

Dim xlChart As Excel.ChartObject
Dim seriesCount As Long
Dim ColorIndex As Long

Dim j As Long


With mWorksheet
    .Rows(rowIndex).RowHeight = constChartHeight

    Set xlChart = .ChartObjects.Add(.Rows(rowIndex).Left, .Rows(2).Top, constChartWidth, constChartHeight)
End With

With xlChart.chart
    .ChartType = ChartType
    .SetSourceData Source:=marrayPOClient, PlotBy:=marrayPOSKU
    .SeriesCollection(1).XValues = marrayPOClient
    .HasTitle = True

    .Legend.Position = LegendPosition
    .Legend.Font.Size = 7.3
    .Legend.Font.Bold = True
    .Legend.Border.LineStyle = xlNone

    .ChartTitle.Characters.Text = ChartTitle
    .ChartTitle.Font.Bold = True

    .Axes(xlValue).TickLabels.Font.Size = 8 ' yAxis Labels
    .Axes(xlCategory).TickLabels.Font.Size = 8 ' xAxis Labels

    .PlotArea.Interior.ColorIndex = PlotAreaColorIndex
    .PlotArea.Interior.ColorIndex = 15
    .PlotArea.Interior.PatternColorIndex = 1
    .PlotArea.Interior.Pattern = xlSolid
End With
End Sub

Is it possible to use array for chart. If possible what are my mistakes.

And im getting an error - it would be useful to tell us exactly what error you're getting. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 13, 2016 at 2:28
Good, that's a start... does a line of code or specific function call get highlighted? — Mathieu Guindon
– Mathieu Guindon, Commented Sep 13, 2016 at 2:55
@Mat'sMug the error occured in this line of code .SetSourceData Source:=marrayPOClient, PlotBy:=marrayPOSKU — Vian Ojeda Garcia
– Vian Ojeda Garcia, Commented Sep 13, 2016 at 3:03
Just be aware that if you use an array there are limits as to how much data it can contain, because you are constrained by the maximum length of the series formula. — Rory
– Rory, Commented Sep 13, 2016 at 7:18

chris neilsen · Accepted Answer · 2016-09-13 04:09:48Z

3

As Mat's Mug says, SetSourceData requires a Range, but you can achieve the result using another method

Replace

.SetSourceData Source:=marrayPOClient, PlotBy:=marrayPOSKU

with

.SeriesCollection.NewSeries
.SeriesCollection(1).Values = marrayPOClient

This will create a new series without a source, then assign the array as the series values

answered Sep 13, 2016 at 4:09

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris H. Over a year ago

I realize this is a hella old question. When I use this method, my second and third values get to the graph but all plot 0. In the select data window the values are correct, but the plotted points are 0. First series works great.

Mathieu Guindon · Accepted Answer · 2016-09-13 03:14:30Z

0

Chart.SetSourceData requires a Range object for its Source parameter, and an XlRowCol enum value for its PlotBy parameter.

I'm assuming both marrayPOClient and marrayPOSKU are arrays as their names imply (you haven't shown where they're declared and how they're assigned, so we can't know their type or value), but you need to supply a Range for the first parameter and, optionally, either xlColumns or xlRows for the second parameter.

answered Sep 13, 2016 at 3:14

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

1 Comment

Vian Ojeda Garcia Over a year ago

that's what I thought too. Is there any other way to do it? I tried using chart.values but still failed. The error says "Invalid Parameter"

Collectives™ on Stack Overflow

Creating VBA Chart using Array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related