Excel VBA Line Color / Marker Line Color

问题:

I'm writing some VBA code to modify Excel charts. For a scatter chart I need to modify the marker line colour and sometimes the line colour of the connecting lines. I can do both manually but when I record a Macro, both actions result in the same code despite the results being very different.

Any idea how to distinguish between a line colour and a marker line colour in code?

This code was created when I recorded myself changing colour of the marker lines

Sub Macro3()
'

    ' Macro3 Macro
    '
    '
        ActiveChart.SeriesCollection(2).Select
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
        End With
    End Sub

This code was created when I recorded myself changing the color of the line connecting the markers

Sub Macro4()
'
' Macro4 Macro
'
'
'Change the Line Color
    ActiveChart.SeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
End Sub

回答1:

The line colour of the connecting lines is Series.Format.Line.ForeColor. The marker line colour is Series.MarkerForegroundColor. But at least with Excel 2007 there is a problem with setting Series.Format.Line.ForeColor. See example:

Sub Macro3()
 Dim oChart As Chart
 Dim oSeries As Series

 Set oChart = ActiveChart
 Set oSeries = oChart.SeriesCollection(2)

 oSeries.Format.Line.Weight = 5 'Line.Weigth works ever

 oSeries.Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 oSeries.Format.Line.Visible = msoTrue
 oSeries.Format.Line.ForeColor.RGB = RGB(0, 255, 0) 'now it works

 oSeries.MarkerSize = 15
 oSeries.MarkerBackgroundColor = RGB(255, 0, 0) 'marker background

 oSeries.MarkerForegroundColor = RGB(0, 0, 255) 'marker foreground (lines around)
End Sub

The ActiveChart is a scatter chart. And this is tested with Excel 2007.

回答2:

From Excel 2013, the line colour and the marker line colour are easy to distinguish, as the Line colour is set using the .Border property, whilst the Marker colours are set using .MarkerBackgroundColor and .MarkerForegroundColor properties.

So the following will give you white markers, with a red border and black connecting lines between them:

ActiveChart.SeriesCollection(1).Select
With Selection
    .Border.LineStyle = xlContinuous
    .Border.Color = RGB(0,0,0)
    .MarkerBackgroundColor = RGB(255, 255, 255)
    .MarkerForegroundColor = RGB(255, 0, 0)
End With

NB: If you make use of Selection.Format.Line.Weight, note this applies to both the borders and connecting line thickness by default

回答3:

You could use

ActiveChart.SeriesCollection(1).MarkerForegroundColor = -2