Creating a chart and then adding a range from where to take the data labels

Question

I've got the following code

range_data_labels = "E" & lrow2 + 1 & ":" & "E" & lrow3
  
'Add data labels
   ActiveChart.ApplyDataLabels
ActiveChart.FullSeriesCollection(1).DataLabels.Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange. _
    InsertChartField msoChartFieldRange, range_data_labels, 0
Selection.ShowRange = True
Selection.ShowValue = False

and I know that the following line doesn't work because I've got "range_data_labels" on it, I'm just not sure on what format I am supposed to include this if I don't want a set range all of the time.

    InsertChartField msoChartFieldRange, range_data_labels, 0

Ps: in my example, range_data_labels = E9:E13 but it will vary depending on some inputs of my code.

Answer 1

According to the documentation here, the second parameter (formula)

Specifies a cell (or range) if the msoChartFieldFormula constant is passed in for the ChartFieldType parameter.

You have passed msoChartFieldRange, which

Specifies the value of a range of data.

as described here.

So as I understand this, and since the formula is a string, you should pass a string representation of the range. In a hard-coded example, "Sheet1!E9:E13".

For something more dynamic and if range_data_labels is a Range object, then use either range_data_labels.Address or range_data_labels.AddressLocal. The latter is probably preferred.

Range.Address

Returns a String value that represents the range reference in the language of the macro.

Range.AddressLocal

Returns the range reference for the specified range in the language of the user. Read-only String.