3

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
  • Your last two with are addressing the same object – Raystafarian Dec 16 '14 at 12:47
  • @Raystafarian, I don't think you read the question. I know that they address the same object. That is the whole point of my question! Why did the macro recording of events that changed two different objects produce a macro that appears to reference the same object twice? And what objects should be referenced in the macro to effect the changes that I made manually? – user02814 Dec 17 '14 at 5:42
  • I have struggled with this. There are gaps in the way VBA handles the formatting of chart elements, when Office 2007 was rushed to market. The deficiencies have never been rectified, other than the broken .TintAndShade being left in for compatibility but replaced in 2010 by .Brightness for functionality. – Jon Peltier Mar 31 '17 at 15:41
4

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

  • Of course, .Brightness is a valid attribute to be modified manually through the Excel UI; it just breaks in VBA. – Jon Peltier Mar 31 '17 at 15:37
  • @JonPeltier Been forever since I wrote this, but IIRC some chart types don't support .Brightness and thus it becomes an undefined reference in those cases; thus the property (or whatever) error thrown by VBA in OP's situation. – hBy2Py Mar 31 '17 at 15:41
  • I don't think it's chart type so much as certain elements. I've only seen .Brightness throw an error when you try to adjust the .Brightness of the fill (background) color of a marker (well, yeah, only certain chart types have markers, but it's all markers and nothing else). It records fine, fails on playing back. Note to self: Try .TintAndShade. – Jon Peltier Apr 1 '17 at 17:05
  • @JonPeltier Could well be. I didn't do a rigorous investigation of what blew up VBA and what worked fine. – hBy2Py Apr 1 '17 at 17:10
  • Yeah, .TintAndShade had no effect. So we can't use either that or .Brightness to adjust the built in color for marker fills. For marker borders, incidentally, both .Brightness and .TintAndShade affect the brightness of the line color, but also revert the marker border to a previous color. So they are essentially useless as well. – Jon Peltier Apr 1 '17 at 19:06
0

This works for me.

   ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
-1

I thought that this worked for me, too, EXCEPT that it turns the fill RED no matter what RGB values I put in.

ActiveChart.FullSeriesCollection(1).Select With Selection.Format.Fill .Visible = msoTrue .ForeColor.RGB = RGB(255, 0, 0) .Transparency = 0 .Solid End With

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.