vba Excel 宏更改了 ActiveChart.SeriesCollection(1).XValues 但文本保持不变
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15596365/
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 macro changed ActiveChart.SeriesCollection(1).XValues but the text remained unchanged
提问by alxyzc
I am very new to VB or VBA, but recent work with Excel requires a huge number of repetitions. That's why I am now considering the VBA macro approach to simplify the process.
我对 VB 或 VBA 很陌生，但最近使用 Excel 工作需要大量重复。这就是我现在考虑使用 VBA 宏方法来简化流程的原因。
The work is simple - to generate charts with given data. But there are hundreds of charts to create, so I used copy and paste to keep the style consistency, and changed the values within afterwards.
工作很简单——用给定的数据生成图表。但是要创建数百个图表，所以我使用复制和粘贴来保持样式的一致性，然后更改其中的值。
Everything worked out pretty well with the data, but not so smooth with the axis label. I was tring to use ActiveChart.SeriesCollection(1).XValues = "=<sheet_name>!$<row_num>$<col_num>"to modify the value to some existing text in the respective cells, but it didn't work out on the chart, i.e., if the label was "total", as is in the A6 cell, in the original chart that I copied from, after selecting the pasted chart and executing the command above, the label in the new chart still says "total" instead of what's in the, say, B6 cell, which for example is "China". But when I right clicked the chart and went into "select data", the "Horizontal (Catagory) Axis Label" is indeed changed into "=sheet1!$B$6", which made me very confused.
数据的一切都很好，但轴标签不是那么顺利。我试图用来ActiveChart.SeriesCollection(1).XValues = "=<sheet_name>!$<row_num>$<col_num>"修改相应单元格中某些现有文本的值，但它在图表上不起作用，即，如果标签是“总计”，就像在原始图表中的 A6 单元格中一样在选择粘贴的图表并执行上面的命令后，我从中复制的标签仍然显示“总计”，而不是 B6 单元格中的内容，例如“ CN ”。但是当我右击图表进入“选择数据”时，“水平（类别）轴标签”确实变成了“=sheet1！$B$6”，这让我很困惑。
Anyone knows how this happened and how to fix this? Is there an "update" method to the chart object or anything that I've been missing?
任何人都知道这是怎么发生的以及如何解决这个问题？图表对象或我遗漏的任何东西是否有“更新”方法？
I am currently using Excel 2010.
我目前正在使用 Excel 2010。
回答by grahamj42
Welcome to the strange world of Excel VBA.
欢迎来到 Excel VBA 的陌生世界。
SeriesCollection().XValuesrequires either a Rangeobject or an array of points, so you must transform your string into a range:
SeriesCollection().XValues需要一个Range对象或一组点，因此您必须将字符串转换为一个范围：
ActiveChart.SeriesCollection(1).XValues = Sheets(<sheet_name>).Range("$<row_num>$<col_num>")
