CODEWITHSUNDEEP
vbaexcel
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

Creating VBA Chart using Array

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.

Upvotes: 1

Views: 4231

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

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.

Upvotes: 0

Related Questions