marker line color with vba

Thicker lines are where you get into problems, and customers often want them for better presentation. A problem with one workaround -- white marker borders -- is the potential for overlap onto other chart objects.

What follows does NOT by any means solve the problem of not being able to programmatically access the proper object for marker borders in VBA in Excel 2010, but another workaround I did not see mentioned is to reduce the size of the markers until the unwanted border + marker size are acceptable, which still allows chart lines to be set to a specified width. Square markers lost some sharpness due to automatically rounded corners. Snippet of code that sets three series differently based on series names:

Dim cht As Chart

Dim srs As Series

Set cht = Me.ChartObjects("chtBudEstAct").Chart

For Each srs In cht.SeriesCollection

    Select Case srs.Name

        Case "Line1"

            With srs

                .Format.Line.ForeColor.RGB = RGB(255, 0, 0)

                .MarkerStyle = xlMarkerStyleSquare

            End With

        Case "Line2"

            With srs

                .Format.Line.ForeColor.RGB = RGB(0, 0, 255)

                .MarkerStyle = xlMarkerStyleCircle

            End With

        Case "Line3"

            With srs

                .Format.Line.ForeColor.RGB = RGB(0, 102, 0)

                .MarkerStyle = xlMarkerStyleTriangle

            End With

    End Select

    With srs

        .Format.Line.Weight = 3

        .MarkerSize = 4

        .MarkerBackgroundColor = .Format.Line.ForeColor

    End With

Next

I have lost count as to how many VBA objects have gone missing or become unusable due to vba "confrusion" such as this (chart line vs. marker line) since 2007, and sincerely wish Microsoft would stop dumbing down the product, or making assumptions about what Excel developers want (need) to be able to do. There are few things less fun than having a customer come back to you, asking that a tool be updated to work in their newly-upgraded version, and have to tell them sorry, it is no longer possible to do what you want.

As you have seen the macro recorder code does not do the job (it formats the line instead of the markers).

I have searched for relevant objects in the object model without success.

I have raised a question with the Microsoft product team to see if it is an omiission from the VBA object model (which should be fixed in the next version) or if it is an undiscoverable part of the object model (which may enable us to proceed given the right assistance).

Thanks for your help Bill, is there any way to use the RGB in this case? I have a variety of different series that I would like to vary the color from dark to light.  Thus the closer the value gets to its maximum the dark it gets, the further away the lighter.  Or would something like tint and shade and brightness work better?

Dave

This gives no lines and markers with red border and yellow inside

Sub test2()

  With ActiveChart.SeriesCollection(1)

    .Border.ColorIndex = xlNone

    .MarkerForegroundColorIndex = 3

    .MarkerBackgroundColorIndex = 6

  End With

End Sub

Quite different from the code the 2010 macro recorder generates; more like what Excel 2003 would have given.