CODEWITHSUNDEEP
vbaexcel
Robin Trietsch
Robin Trietsch

Reputation: 1842

Manually create data range for chart in VBA

I've looked around at Stackoverflow and other websites, but I haven't found the solution to the following:

I would like to set the data for a chart manually in VBA. I do not want a reference to a worksheet, e.g.

MyChart.SeriesCollection(1).XValues = "=Sheet1!$F$25:$G$25"

What I want is something like:

MyChart.SeriesCollection(1).XValues = {Value1,Value2,Value3,...}

But I have no clue how to set the data this way. Any help is much appreciated!

Upvotes: 0

Views: 954

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Do this with an array of values:

Dim xVals() As Variant

xVals = Array(30,50,70,90,25)


MyChart.SeriesCollection(1).XValues = xVals

If you want to use the values from the sheet, without a reference to the sheet, you could modify it slightly. Using this method, we can take the values from the worksheet, store them in an array and use the array to populate the chart data. The chart will then reflect the data, but it will not update as the data changes, so you could use this method to prevent users from inadvertently changing a chart's data:

Dim xVals() As Variant
xVals = Sheet1.Range("F25:G25").Value
MyChart.SeriesCollection(1).xValues = xVals

Upvotes: 3

Related Questions