CODEWITHSUNDEEP
excelvba
Carlos80
Carlos80

Reputation: 433

VBA - Dynamic Charts

I have a really useful piece of VBA code that creates dynamic charts depending on how many rows of data are populated by the users dataset.

With Sheets("DynamicCharts")
    LastRow = .Range("R" & .Rows.Count).END(xlUp).row
    Set Rng1 = .Range("R2:R" & LastRow & ", S2:S" & LastRow)
    ShName = .Name
End With
With Sheets("Property Details").ChartObjects("Year").Chart
    .SetSourceData Source:=Rng1
End With

However I have noticed that when I have values of only one row (see below):

enter image description here

my piece of code doesn't read the category name. It populates the chart with 1 rather than the name (see below)

enter image description here

I suspect it is how it is setting the range:

Set Rng1 = .Range("R2:R" & LastRow & ", S2:S" & LastRow)

and its not liking the "R2:R2" in the chart but I'm not sure how you build a work around in

Thanks

Upvotes: 1

Views: 120

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

The issue - Why is this?

If you set the data source with .SetSourceData Source:= you actually let Excel guess how the data in that range is arranged. If it is more than one row in that range, then Excel can analyze and guess that pretty good.

But with only one row of data Excel cannot determine properly which of the text data the chart title is and which the row/category name.

Solving it …

That means you must be more precise in your definition.

For example if you include row 1 the guessing gets a bit better: 

Set Rng1 = .Range("R1:R" & LastRow & ", S1:S" & LastRow)

but then chart title and category name flip to Excel's default and the result is:

enter image description here
Image 1: The chart title and the category name flipped. Excel's guess was not the expected result.

If you are more precise and tell Excel exactly which data is where in that range, then you will get a precise result:

With Sheets("Property Details").ChartObjects("Year").Chart
    .SetSourceData Source:=Rng1
    .FullSeriesCollection(1).Name = "='" & ShName & "'!$S$1"                     '≙ "=DynamicCharts!$S$1"
    .FullSeriesCollection(1).Values = "='" & ShName & "'!$S$2:$S$" & LastRow     '≙ "=DynamicCharts!$S$2:$S$" & LastRow
    .FullSeriesCollection(1).XValues = "='" & ShName & "'!$R$2:$R$" & LastRow    '≙ "=DynamicCharts!$R$2:$R$" & LastRow
End With

enter image description here
Image 2: With a precise definition where which data is, Excel doesn't need to guess and you get the desired result.

Conlusion

Excel is very patient if you are not giving precise instuctions what exactly to do. So it will guess and present a result (which might not be what you expected). But the more precise the instructions you give, the less Excel guesses and the more precise and reliable the result is.

Best practice: Always be as precise as possible to avoid surprises :)

Upvotes: 3

Related Questions