Calling on a VBA procedure that generates a chart using ARRAY data

Question

So, I have created an array, and I am trying to put the data into a chart... but I have no clue how to make the chart take on the data from an array I generated, rather than from a range. I coded for a chart uding a Range from an excel sheet.. but I can't see how to make all of that happen when I have this array..

I have two sub procedures. Inside one of them I want to use

Call ChartNew2(myArray)

How can I do that? I tried in this way and I wasn't successful either...

Sub ChartNew2(result2 As Variant)
Dim i As Integer
ReDim result2(1 To 4, 1 To 1)
Charts.Add
    For i = LBound(result2, 1) To UBound(result2, 1)
        result2(i, 1) = result2
    Next i
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.Location Where:=xlLocationAsObject

    With ActiveChart
                .HasTitle = True
                .Axes(xlValue, xlPrimary).HasTitle = True
        End With


    With ActiveChart.Axes(xlValue)
                .HasMajorGridlines = True
    End With

    ActiveChart.HasLegend = False
    ActiveChart.PlotArea.Select

    Selection.Interior.ColorIndex = xlAutomatic
    ActiveChart.Axes(xlValue).Select

    With ActiveChart.Axes(xlValue)

            .MaximumScale = 1

    End With

End Sub

Is it acceptable to drop your array values on a page, create a range against it and get the chart to build from the range? — Nick.Mc
– Nick.Mc, Commented Apr 26, 2013 at 7:30
I wouldn't like to do it, unless I can do it and then let the values in those ranges disappear, cause they would look VERY ugly. — seigna
– seigna, Commented Apr 26, 2013 at 7:31
You can probably hide the worksheet with the values on it. Then you wouldn't have to behold its ugliness. — Nick.Mc
– Nick.Mc, Commented Apr 26, 2013 at 7:33

Nick.Mc · Accepted Answer · 2013-04-26 07:53:25Z

1

This link: http://www.excelforum.com/excel-charting-and-pivots/400227-making-charts-from-arrays-in-vba.html

says to use this syntax:

With ActiveChart.SeriesCollection(1)
    .XValues = MyXArray
    .Values = MyYArray
    .Name = MyName
End With

If you're having troubles passing arrays, I think this is the syntax if your array is an integer array.

Sub ChartNew2(result2() As Integer)

edited Apr 26, 2013 at 7:53

answered Apr 26, 2013 at 7:48

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

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

9 Comments

seigna Over a year ago

Yes, I tried it but the problem is the array values are generated by a different Sub procedure... And then I call on them. I don't know how to refer correctly to that array...

Nick.Mc Over a year ago

So you've validated that that code works if the arrays are local? S the real problem is passing arrays, not using them in charts?

seigna Over a year ago

I actually have my array as a Variant, and it works as a Variant.. Integer wouldn't cause I don't have integers.

Nick.Mc Over a year ago

How do you chart a variant? Surely they are numbers of some kind. Anyway, does it work if you use Variant instead of Integer?

Nick.Mc Over a year ago

Oh now that I do some reading it seems you know more than me :). When you use your code as-is, what line does the error occur on?

|

Collectives™ on Stack Overflow

Calling on a VBA procedure that generates a chart using ARRAY data

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related