Excel- vba- 为宏生成的图表预设图表轴标签
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23050214/
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- vba- to preset chart axis labels for a macro- generated chart
提问by JaroM
I have a problem. Let's say, for simplicity, I'm processing a simple two colunms of data repeatedly and the output is a plot, so I recorded a macro. But the problem is I want to set the axis labels on the chart, as I want, so as they have (the same) concrete names, every times I run the macro. But the vba- macro code does not record any information about the difference between the Xand the Yaxis, and the result is, that the one axis label is overwritten or, there are two same axis labels in the macro- generated chart. I add: code sample (in the code, I removed all unnecessary data, and such, that prevent the macro from running next time). What I want (I apologize for such rude formulation, but I just wanted to strictly state, what is my idea): I would like to have a solution, such, that every time I run the macro, I have a different chart, but with the same axis label names, i.e., X-axis: U [V], and Y-axis: I [A]
我有个问题。让我们说，为简单起见，我重复处理简单的两列数据，输出是一个图，所以我记录了一个宏。但问题是我想在图表上设置轴标签，因为它们有（相同的）具体名称，每次我运行宏。但是 vba- 宏代码并没有记录任何关于X和Y之间差异的信息轴，结果是一个轴标签被覆盖，或者在宏生成的图表中有两个相同的轴标签。我添加：代码示例（在代码中，我删除了所有不必要的数据等，以防止宏下次运行）。我想要什么（我为这种粗鲁的表述道歉，但我只是想严格说明，我的想法是什么）：我想要一个解决方案，例如，每次运行宏时，我都有不同的图表，但是具有相同的轴标签名称，即 X 轴：U [V] 和 Y 轴：I [A]
code:
代码：
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.ApplyLayout (1)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""I-V char"""
ActiveChart.SeriesCollection(1).XValues = "=hviezd1!$D:$D3"
ActiveChart.SeriesCollection(1).Values = "=hviezd1!$E:$E3"
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "I [A]"
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "U [V]"
I tried to record the macro by different ways such as throught the tool bar in excel (also more ways throught this), throught the keyboard, but none recorded code contains any information, that could distinguish between X and Y axis.
我尝试通过不同的方式记录宏，例如通过excel中的工具栏（还有更多的方式通过这个），通过键盘，但是没有记录的代码包含任何可以区分X轴和Y轴的信息。
I'm using a 2007 excel (as I read before in other questions, the 2007 excel chart macro recorder is pretty poor and the problem, I'm talking about might be caused by this), but I'm looking for any good answer that can help solve my problem. Thank's for your answer.
我使用的是 2007 excel（正如我之前在其他问题中读到的，2007 excel 图表宏记录器非常糟糕，我所说的问题可能是由此引起的），但我正在寻找任何好的答案这可以帮助解决我的问题。感谢您的回答。
回答by prm
Here is a nice VBA script for drawing a bar chart. you need to make changes in cell values to make it work for you. For me it worked good. This is a kind of note of thanks to all the net users from who I learnt VBA (not expert level yet)....
这是一个用于绘制条形图的不错的 VBA 脚本。您需要更改单元格值以使其适合您。对我来说效果很好。这是对我学习 VBA 的所有网络用户（还不是专家级别的）的一种感谢。...
Sub yourMethodName()
Dim myChart As Chart, cht As ChartObject
Dim rngChart As Range, desinationSheet As String
destinationSheet = ActiveSheet.Name
Set myChart = Charts.Add
Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:=destinationSheet)
myChart.SetSourceData Source:=Range("D37:E38").CurrentRegion, PlotBy:=xlColumns
myChart.ChartType = xlColumnClustered
ActiveSheet.ChartObjects(1).Activate
Set cht = ActiveChart.Parent
'Y-axis value is set up below
cht.Chart.Axes(xlValue).MinimumScale = 1
cht.Chart.Axes(xlValue).MaximumScale = 1
' X axis and Y axiz titles
With cht
'X axis title
cht.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
cht.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "All CRs"
'y-axis title
cht.Chart.Axes(xlValue, xlPrimary).HasTitle = True
cht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% Complete"
End With
'Chart area is set up below
Set rngChart = Range("B12:G33")
cht.Left = rngChart.Left
cht.Top = rngChart.Top
cht.Width = rngChart.Width
cht.Height = rngChart.Height
Range("A37").Select
End Sub
回答by Adach1979
You can use this:
你可以使用这个：
With ActiveChart
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis"
End With
