How to use Excel 2010 VBA to set series line color, marker fill, and marker line color

I am trying to write an Excel 2010 VBA subroutine to format charts according to a predefined (i.e., defined by me) standard. The particular attributes that I want to be able to set are merely the attributes that become available through the Format Data Series window that opens when one double-clicks on a data series.

In an effort to discover the names of the various properties that I would need to set, I recorded as a macro the changes that I was making to the style of the data series. However, although I can set the Line Color, and the Marker Line Color to different colours via the Format Data Series window, the recorded macro (annotated below) refers to identically named (indistinguishable) objects for the Line Color and the Marker Line Color.

In addition, when I actually run the macro, there are two problems. First, despite the fact that the recorded macro refers to the marker-fill property .ForeColor.Brightness, that line produces an error when the macros is executed. The error says “Method Brightness of object ColorFormat failed". Second the recorded code actually simultaneously sets the marker line colour and the main line for the series, so in the recorded code, they are both first set to what I hoped would be the Marker Line Color, and then both set to what I wanted for the main Line Color.

How do I set the Marker Fill, Marker Line Colour, and Line Colour.

Sub Macro1()
'
' Macro6 Macro
' On Sheet 1 there is a single embedded chrt 
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.SeriesCollection(1).Select
  With Selection.Format.Fill
    .Visible = msoTrue

    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
'    The following (recorded line produces an error)
    .ForeColor.Brightness = 0.5
    .Transparency = 0
    .Solid
  End With
  With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent5

    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0.400000006
    .Transparency = 0
  End With
  With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorAccent6
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.5
    .Transparency = 0

  End With
End Sub

Answer

It looks like these formatting options are implemented somewhat awkwardly in Excel 2010 and 2013:

1. The line color and properties are set as the macro code has recorded them, using the Series.Format object and its children (Fill, Glow, Shadow, etc. [1]).


2. Marker fill color for all markers in the series is set with Series.MarkerBackgroundColor or Series.MarkerBackgroundColorIndex. Similarly, marker line color for all markers in the series is set with Series.MarkerForegroundColor or Series.MarkerForegroundColorIndex. [2]


3. Alternatively, marker fill and line colors can be set individually through the Series.Points(n).Format.Fill and Series.Points(n).Format.Line objects. However, at least in Excel 2013, changing Series.Points(n).Format.Line.ForeColor also changes the color of the line segment immediately preceding the relevant data point.

The properties of #2 look like holdovers from the prior Excel DOM, though with expanded functionality in terms of .MarkerForegroundColor and .MarkerBackGroundColor accepting any RGB value. Those of #3 are consistent with the new level of configurability found in Excel 2010 and newer, but they appear buggy. One annoying aspect of the bugginess is that it appears that the marker line colors are problematically intertwined with the series line color--as far as I can tell, it is impossible to change the line color without also affecting the marker line colors, and vice versa. In particular, it would appear that it is impossible to achieve a uniform series line color while also applying point-by-point variations in marker line color using VBA. (Again, I'm testing here in Excel 2013; 2010 may behave differently.)

In any event, in situations where point-by-point color tweaking isn't needed, a helper function such as the following might be useful for making changes to the line color without affecting the marker fill or line colors (here, newLineColor is specified as a Long RGB value [3]):

Sub ChangeLineColorOnly(srs as Series, newLineColor as Long)
    Dim oldMkrFill as Long, oldMkrLine as Long

    ' Store old marker colors

    oldMkrFill = srs.MarkerBackgroundColor
    oldMkrLine = srs.MarkerForegroundColor

    ' Set the series ForeColor
    srs.Format.Fill.ForeColor.RGB = newLineColor

    ' Restore the old marker colors
    srs.MarkerBackgroundColor = oldMkrFill
    srs.MarkerForegroundColor = oldMkrLine


End Sub

An alternative version of the above helper function could easily be written to accommodate colors specified as a SchemeColor [4], or to only store the marker line color, etc.

As for the .ForeColor.Brightness glitch, it presumably resulted from careless recoding of the 'Record Macro' functionality in developing the Excel 2010 release. It probably should only have been inserted into the recorded VBA code for certain types of charts where .Brightness is a valid attribute to be modified.

[1] http://msdn.microsoft.com/en-us/library/office/ff839279(v=office.14).aspx
[2] http://msdn.microsoft.com/en-us/library/office/ff840677(v=office.14).aspx
[3] http://msdn.microsoft.com/en-us/library/zc1dyw8b%28v=vs.90%29.aspx
[4] http://msdn.microsoft.com/en-us/library/office/ff836764(v=office.14).aspx