vba Excel：如何向图表添加自定义网格线？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16059216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: How can I add custom gridline to a chart?
提问by Eduard Florinescu
How can I add a custom horizontal line that has a label and it is at the exact same level as the first column in the chart (see the screenshot below).
如何添加具有标签的自定义水平线，并且它与图表中的第一列处于完全相同的级别（请参见下面的屏幕截图）。
Can this be done in VBA?
这可以在VBA中完成吗？
回答by David Zemens
This could be done in VBA, or it could be done without VBA:
这可以在 VBA 中完成，也可以在没有 VBA 的情况下完成：
http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html
http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html
This method involves creating a secondary Y-axis, and plotting another series of data in a "line" on the second axis.
此方法涉及创建辅助 Y 轴，并在第二个轴上的“线”中绘制另一系列数据。
This is a fairly clean solution.
这是一个相当干净的解决方案。
Otherwise with VBA you would need add a shape/line to the chart (important to add it to the chartObject and not to the Worksheet).
否则，使用 VBA，您需要向图表添加形状/线条（重要的是将其添加到图表对象而不是工作表）。
Then compute the height of points and make the line's .Left= the chart's .PlotArea.Leftand make the line's .Width= to the chart's .PlotArea.Width. Then set the line's .Topvalue based on the chart's .PlotArea.Heightminus the "height" you calculated for the point.
然后计算点的高度并使线的.Left= 图表.PlotArea.Left并使线的.Width= 成为图表的.PlotArea.Width。然后.Top根据图表.PlotArea.Height减去您为该点计算的“高度”设置线的值。
回答by SeanC
using vba, you can add a new series:
使用 vba，您可以添加一个新系列：
With ActiveChart.SeriesCollection.NewSeries
.Values = "={6.9,6.9,6.9,6.9}"
'create string beforehand if number and values are unknown
.ChartType = xlLine
'and whatever other formatting is needed
End With
not using VBA, you can add a new column to the data, and put all of it equal to the first item, using =$B$2in each cell to add the line to the graph
不使用VBA，您可以向数据中添加一个新列，并将所有列与第一项相等，=$B$2在每个单元格中使用将线添加到图形中
