添加水平轴标签 - VBA Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35806095/
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
Add horizontal axis labels - VBA Excel
提问by Jimbo
I have a macro that creates a graph. I want VBA to read a range from the spreadsheet and use the values for horizontal axis labels. Basically I want to make this graph:
我有一个创建图形的宏。我希望 VBA 从电子表格中读取一个范围并使用水平轴标签的值。基本上我想做这个图：
look like this (add the months on the bottom)
看起来像这样（在底部添加月份）
Thanks!
谢谢！
Macro:
宏：
Sub AddChartSheet()
'Variable declaration
Dim chtChart As Chart
Dim name1 As String
'Name is currently used for the title of the new tab where the chart is created and the chart title
name1 = "AHU-10-8"
'Create a new chart.
Set chtChart = Charts.Add
With chtChart
'.Name is the name of the tab where the new Chart is created
.Name = name1
.ChartType = xlLine
'Link to the source data range.
.SetSourceData Source:=Sheets(3).Range("A1:B5861"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = name1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Valve Position (-)"
myFileName = name1 & ".png"
chtChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName, Filtername:="PNG"
End With
End Sub
回答by rdt0086
To adjust the data series you are using for the Date (horizontal axes). You can either add the following
调整用于日期（水平轴）的数据系列。您可以添加以下内容
ActiveSheet.ChartObjects("Chart 15").Activate
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$D:$D"
Note: you will first need to select the chart and adjust the range that I have to the range that you need it to be.
注意：您首先需要选择图表并将我的范围调整到您需要的范围。
OR you can add
或者你可以添加
.SeriesCollection(1).XValues = "=Sheet1!$D:$D"
Between your code
在你的代码之间
.SetSourceData Source:=Sheets(3).Range("A1:B5861"), _
PlotBy:=xlColumns
and
和
.HasTitle = True
回答by rdt0086
Can you share your macro or workbook?
你能分享你的宏或工作簿吗？
I am not sure how you have your data setup, but you can change the format of the data that you have selected for your horizontal labels to be a date format. Or, in VBA you can change the selection to a number format of "mmmm" to just show the months.
我不确定您如何设置数据，但是您可以将您为水平标签选择的数据格式更改为日期格式。或者，在 VBA 中，您可以将选择更改为“mmmm”的数字格式以仅显示月份。
Selection.NumberFormat = "mmmm"
回答by rdt0086
The following should work. Remember to adjust the chart name in the following code
以下应该工作。记得调整下面代码中的图表名称
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "mmmm"
