Excel VBA - 如何为图表系列设置线条样式？

I would like the plot lines in a chart to be either solid, circle dot, or square dot based upon a certain criteria specified by the user. I can successfully set the line color and marker style for the plot using a macro, but I cannot seem to find the object which holds the value for the plot line style property. I have tried using the record macro function, but changing the line style in the properties windows does not show up in the code, and running the recorded macro has no effect.

我希望图表中的绘图线可以是基于用户指定的特定条件的实线,圆点或方点。我可以使用宏成功设置绘图的线条颜色和标记样式,但我似乎无法找到保存绘图线条样式属性值的对象。我已尝试使用记录宏功能,但更改属性窗口中的线条样式不会显示在代码中,并且运行录制的宏无效。

Any help is greatly appreciated!

任何帮助是极大的赞赏!

3 个解决方案

#1

Create a chart with 255 data series, run the code (and do other formatting as necessary). Then save it as a template.

创建包含255个数据系列的图表,运行代码(并根据需要执行其他格式设置)。然后将其另存为模板。

Sub dd()

Dim wb As Workbook
Set wb = Application.ActiveWorkbook

Dim myChart As Chart
Set myChart = wb.Charts("Chart5")

Dim mySeries As series

For Each mySeries In myChart.SeriesCollection
    mySeries.Format.Line.Weight = 1#
Next

End Sub

#2

YourChartSeries.Border.LineStyle = [some value from the XlLineStyle enumeration]

UPDATE: recording in XL 2010 I get this -

更新:在XL 2010录制我得到了 -

ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineSysDot
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineSysDash
End With

Which might be what you're looking for.

这可能是你正在寻找的。

#3

If you have a line chart, I ended up creating a switch statement because I couldn't figure out how to make an array of "Name" variables. See list of line types here

如果你有折线图,我最终创建了一个switch语句,因为我无法弄清楚如何创建一个“Name”变量数组。请在此处查看线型列表

For j = i To num_lines_to_plot
        count = count + 1
        ActiveChart.SeriesCollection.NewSeries
        With ActiveChart.SeriesCollection(j)
            .Name = _
                "='"**... (you'll need to fill this in)**
            'create gradient
            .Format.Line.ForeColor.RGB = RGB(255, 20 * count, 0)
            'modify linetype
            If count > 4 Then
                line_type = count Mod 4
            Else
                line_type = count
            End If

            Select Case line_type
                Case Is = 1
                    .Format.Line.DashStyle = msoLineSolid
                Case Is = 2
                    .Format.Line.DashStyle = msoLineSquareDot
                Case Is = 3
                    .Format.Line.DashStyle = msoLineDash
                Case Is = 4
                    .Format.Line.DashStyle = msoLineLongDash
                End Select

        End With
    Next

#1