我正在尝试为散点图设置一个宏,您可以在其中切换图例中数据集的位置并设置标记的格式。
这是我的宏:
Sub FormatLegend
Dim ChtObj As ChartObject
Set ChtObj = Worksheets("Plot_1").ChartObjects("Diagramm 1")
With ChtObj
With .Chart.SeriesCollection(1)
.PlotOrder = 3
End With
With .Chart.SeriesCollection(4)
.Format.Fill.Visible = msoTrue
.Format.Line.Visible = msoFalse
.Format.Fill.BackColor.RGB = RGB(146, 208, 80)
.MarkerSize = 4
.MarkerStyle = 2
.Weight = 0.75
End With
End Sub
我不明白如何区分line和marker。当我设置为.Format.Line.Visible = msoFalse
时,整行代码就消失了。我要标记保持可见。
如何使这条线不可见,而不是标记?此外,我希望将标记的宽度设置为0.75,并且不应用填充。
发布于 2019-12-19 21:58:34
对我来说,这是可行的:
Sub Add_colour_scale_to_scatter()
chart_name = "Chart 2"
Set my_chart = ActiveSheet.ChartObjects(chart_name).Chart
Set my_series = my_chart.FullSeriesCollection(1)
my_colour = RGB(0, 176, 80) 'RGB(146, 208, 80)
Debug.Print my_colour
For i = 1 To my_series.Points.Count
my_series.Points(i).Select
With Selection 'my_series.Points(i)
.MarkerForegroundColor = my_colour 'Border colour of the points
.MarkerBackgroundColor = my_colour 'Colour of the points themselves
.Format.Line.Weight = 0 'border of the point 0pt
.Format.Line.Visible = msoFalse 'border of the point not visible
.Format.Line.Transparency = 1 'border of the point is completely transparent
.Border.Color = my_colour 'colour of the line between points
.Border.LineStyle = xlLineStyleNone 'line between points is none. Others: 'xlContinuous 'continous line 'xlDot 'dotted line
.Format.Fill.Visible = msoTrue 'the point is visible
.Format.Fill.Solid 'the point has a solid fill
.Format.Fill.ForeColor.RGB = my_colour
.Format.Fill.Transparency = 0.3
.MarkerStyle = 8 'round points
.MarkerSize = 5 'size of the points
End With
If i Mod 100 = 0 Then
DoEvents
'Debug.Print i
Application.StatusBar = i & " of " & my_series.Points.Count
End If
Next i
MsgBox "done"
End Sub
问题是,当您录制一个宏时,它为您提供了相同的代码,用于更改点之间的直线和更改点周围的直线。
我相信.Format.Line.Visible = msoFalse
既删除了点之间的线,也删除了点周围的线。这有点违反直觉。对我来说,按照这个顺序进行操作并使用.Border.LineStyle = xlLineStyleNone
似乎是最好的方法。(即,首先使用.Format.Line.Visible = msoFalse
设置点之间和周围两条线的边界,然后使用.Border.LineStyle = xlLineStyleNone
仅设置点之间的线。对我来说很有效)
这里还有一个示例文件:https://drive.google.com/file/d/1HkeJVgKeFeCuj2ItRn2s90ozy41zlCVL/view?usp=sharing
例如,如果将行.Border.LineStyle = xlLineStyleNone
更改为行.Border.LineStyle = xlContinuous
,则输出如下:
如果您将它设置回.Border.LineStyle = xlLineStyleNone
,那么您将不会得到以下内容之间的行:
(请注意,我使用了一个动态函数来给点着色-你可以在https://gist.github.com/Alex-ley/6fdaddda2b000072f70d98f90111a97e和链接文件中看到这一点)
这里的所有xlLineStyles:https://docs.microsoft.com/en-us/office/vba/api/excel.xllinestyle
https://stackoverflow.com/questions/59406422
复制相似问题