Solved: Excel Chart: How to Label a Chart Series using VBA

[MWE]

I am running Excel 2003. I have an XY scatter plot where Y1 and Y2 are plotted against X. I am having difficulty labeling either of the Y series.

When I am not sure how to do something in VBA, I normally turn on the Macro recorder, do what I want manually and then tweak the macro code.

So, I turned on the macro recorder, selected one of the Y's, entered the name I wanted surrounded by double quotes in the =SERIES( ... at the top of the current window and it worked fine and the Legend box was updated. The key line in the macro is
[vba]ActiveChart.SeriesCollection(1).Formula = "Desired Name"[/vba] That did not make much sense, but I tried it anyway. When I inserted that line into my code, the Chart Legend info did not change, it still indicated "Series 1". Rather the text "Desired Name" appeared in a text box in the middle of the chart. I fiddled around with this and could not get it to work.

So I tried a different approach. I turned on the Macro Recorder, selected the chart; in the Chart drop down menu, I selected Source Data and then the Series Tab. In the Name box for Series 1, I entered ="Desired Name", ... Again, this did what I wanted and the Chart Legend info was updated correctly. I then looked at the code the macro recorder had for this approach and the key line was [vba]ActiveChart.SeriesCollection(1).Name = "=""DesiredName"""[/vba] When inserted into my code, that failed with an error. When I poked around the Object Browser, I discovered that SeriestCollection does not have a child called Name.

Both methods work manually, but neither worked programatically. So, how do I label a data series using VBA?

[frank_m]

Try
[VBA]
Dim Srs As Series

Set Srs = ActiveChart.SeriesCollection(1)

Srs.Name = "Desired Name"
[/VBA]

[MWE]

Try
[vba]
Dim Srs As Series

Set Srs = ActiveChart.SeriesCollection(1)

Srs.Name = "Desired Name"
[/vba]

Thanks for the reply. That suggestion worked (almost). It works for the SeriesCollection(1) but not for SeriesCollection(2). The chart is an XYscatterplot with Y1, Y2 and X. With the appropriate Dim statement, the code is[vba] Set srs = ActiveChart.SeriesCollection(1)
srs.Name = "Name 1"
Set srs = ActiveChart.SeriesCollection(2)
srs.Name = "Name 2"
[/vba] The first 3 lines are OK, the system halts on the last line with "Unable to set the name property of the series class"

I have run into the same problem, i.e., errors when attempted to do anything with SeriesCollection(2). For example, the default marker size is 5. If I manually double click on either Y1 or Y2, I can easily set the marker size to what I want. If I have the Macro recorder turned on while I do that, I get:
[vba] ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 2
.Shadow = False
End With

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 2
.Shadow = False
End With
[/vba] The first block runs fine, the second causes an error on the select line; the error is "Select methods of the series class failed"

[frank_m]

Hi MWE

Both Series (1) & (2) get named without error when I try it.

Did you Dimension the variable Srs with both of your test's ?
[vba]
Option Explicit '<-- Option Explicit at the top of the Module helps detect issues
Sub Macro3()
Dim Srs As Series

Set Srs = ActiveChart.SeriesCollection(1)

With Srs
.Name = "Name One"

With .Border
.LineStyle = xlContinuous 'xlNone
.Weight = xlHairline
End With
End With

Set Srs = ActiveChart.SeriesCollection(2)

With Srs
.Name = "Name Two"

With .Border
.LineStyle = xlContinuous 'xlNone
.Weight = xlHairline
End With
End With
End Sub
[/vba]
It's more effficient, (occasionally necessary), to edit code created by the macro recorder to eleminate the use of select and selection.

[MWE]

Hi MWE

Both Series (1) & (2) get named without error when I try it.

Did you Dimension the variable Srs with both of your test's ?
[vba]
Option Explicit '<-- Option Explicit at the top of the Module helps detect issues
Sub Macro3()
Dim Srs As Series

Set Srs = ActiveChart.SeriesCollection(1)

With Srs
.Name = "Name One"

With .Border
.LineStyle = xlContinuous 'xlNone
.Weight = xlHairline
End With
End With

Set Srs = ActiveChart.SeriesCollection(2)

With Srs
.Name = "Name Two"

With .Border
.LineStyle = xlContinuous 'xlNone
.Weight = xlHairline
End With
End With
End Sub
[/vba] It's more effficient, (occasionally necessary), to edit code created by the macro recorder to eleminate the use of select and selection.

Thanks for the reply:
Yes, I Dimmed the variable srs
I do edit out the extraneous items in the code created by the macro recorder -- did not seem necessary for this post
The elimination of Select and Selection is interesting. I have found that using select and selection often solves problems. But I will try your suggestions.

[frank_m]

Hi again MWE,

If that doesn't help, could you post a workbook and chart

A sample chart and sample source data and a desription of everything the code needs to do.

[MWE]

Hi again MWE,

If that doesn't help, could you post a workbook and chart

A sample chart and sample source data and a desription of everything the code needs to do.

Thanks again. I finally figured out what was wrong. SeriesCollection(2) was not completely defined. The proc that creates the chart creates an XYscattergram with X, Y1 and Y2. When the chart is created, the range associated with Y2 is blank. The proc uses Excel's poly fit capability to determine a 3rd order poly that fits Y1 = F(X) and then uses the poly to create Y2. As soon as I moved all the chart tweaking code to a point after the creation of Y2 (and associated population of the relevant xlsheet, everything works fine. Seems so obvious now