Excel 2016 VBA: Set Source Data for a Chart to a Named Table

Question

The title is pretty self-explanatory.

I have a named table, Table_Unit_2_Data, which I would like to set as the source data for a chart that will be created using VBA.

During the recording of a macro I selected the entirety of the table, and inserted a chart. This is the code that I got (Build is the name of the Sheet):

Sub Test()
    Range("Table_Unit_2_Data[#All]").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
    ActiveChart.SetSourceData Source:= Range("Build!$Y$1:$AD$2")
End Sub

Well, for one thing, as you can see, a specific $A$1 range is passed into the SetSoureData. This will not work because the range of Table_Unit_2_Data will change.

I attempted this:

With Sheet2.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers)
    .Chart.SetSourceData (Sheet2.Range("Table_Unit_2_Data[#All]"))
End With

But then I get the error "Object Required".

I can't seem to phrase my search queries in such a way as to find relevant answers to this specific question on the internet so I apologize for asking what is likely a redundant question. If someone could help me with this problem I would be greatly appreciative and if anyone has a good article or source online for information regarding the nuances of chart creation within VBA that would also be very helpful.

Thank you.

When you enclose an argument within brackets, you force the argument to be passed by value. So here an array of values is passed to SetSourceData instead of a range object. Therefore, remove the brackets from your argument --> .Chart.SetSourceData Sheet2.Range("Table_Unit_2_Data[#All]"). — user6585761
– user6585761, Commented Sep 5, 2017 at 21:08

Daniel McCracken · Accepted Answer · 2017-09-06 05:19:47Z

In addition to what Domenic said (which is correct + would cause the "Object Required" error), your code doesn't make it clear what "Sheet2" is, except that it's the codename of some sheet. From the recorded macro, I can infer that the actual table is on a sheet called "Build", so another possibility to consider is that Sheet2 isn't actually the codename of Sheets("Build"). Again, I can't actually tell from the code provided.

While I do like using sheet codenames, I'd strongly recommend against using them if you're not going to make the names descriptive.

FWIW, there's another way to reference table ranges that's a little more flexible, especially if you're going to be referring to the table elsewhere in the code. Just make a ListObject variable:

Dim UnitTable2 As ListObject
Set UnitTable2 = Sheets("Build").ListObjects("Table_Unit_2_Data")

And you'll be able to reference any part of the table really easily:

Dim rng As Range
'Reference the whole table, including headers:
Set rng = UnitTable2.Range
'Reference just the table data (no headers):
Set rng = UnitTable2.DataBodyRange
'Reference just the data in a single column:
Set rng = UnitTable2.ListColumns("Col1").DataBodyRange
'Reference the headers only
Set rng = UnitTable2.HeaderRowRange

Collectives™ on Stack Overflow

Excel 2016 VBA: Set Source Data for a Chart to a Named Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related