vba 使用宏在excel中格式化图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12410818/
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
Using macros to format charts in excel
提问by David Ian Walker
I'm trying to create a macro that will format charts in Excel so that all of my charts have the same formatting, but different data. Usually I would just paste the formatting, but these charts were made using a plugin and they do not appear to be compatible with 'paste formats'. Each chart is in a different worksheet along with the chart data.
我正在尝试创建一个宏来格式化 Excel 中的图表，以便我的所有图表具有相同的格式，但数据不同。通常我只会粘贴格式，但这些图表是使用插件制作的，它们似乎与“粘贴格式”不兼容。每个图表与图表数据一起位于不同的工作表中。
My charts have 3 series. Each series has a particular format e.g. series 1: circular marker, size-3pt, no marker line, green fill. series 2: circular marker, size-3pt, black marker line, yellow fill. series 3: circular marker, size-3pt, no marker line, red fill.
我的图表有 3 个系列。每个系列都有特定的格式，例如系列 1：圆形标记，大小 3pt，无标记线，绿色填充。系列 2：圆形标记，大小 3pt，黑色标记线，黄色填充。系列 3：圆形标记，大小 3pt，无标记线，红色填充。
Also, the charts need to be a specific size for presentation (no sure exact sizes yet, but presumably that should be easy to edit).
此外，图表需要具有特定的展示尺寸（还不确定确切的尺寸，但大概应该很容易编辑）。
These are the only parameters that I need to change from the default in order to get the charts to look how I want them. Can anyone help?
这些是我需要从默认值更改的唯一参数，以使图表看起来像我想要的样子。任何人都可以帮忙吗？
回答by Michael
Chart formatting can be done fairly easily with VBA:
使用 VBA 可以很容易地完成图表格式化：
Dim chChart as chart
set chChart = Thisworkbook.Sheet("Sheet1").ChartObjects("Chart1").Chart
With chChart
seriescollection(1).Markerforegroundcolor = rgb(255,255,255)
seriescollection(1).Markerbackgroundcolor = rgb(255,255,255)
seriescollection(1).interior.color = rgb(255,255,255)
End With
The above code should give you an idea of how to write the macro, substituting the correct sheet and chart names. Ideally you would also use some loops to loop through you charts and series within the charts
上面的代码应该让您了解如何编写宏，替换正确的工作表和图表名称。理想情况下，您还可以使用一些循环来遍历图表和图表中的系列
For each chart in Thisworkbook.Sheet("Sheet1").ChartObjects
For each series in chChart.seriescollection
To get the exact values you want to change I would suggest either recording a macro of you making the changes manually or using the Editor Object Browser (F2 in the VBA Editor) to find the likely values.
要获得您想要更改的确切值，我建议您手动录制您进行更改的宏或使用编辑器对象浏览器（VBA 编辑器中的 F2）来查找可能的值。
