Excel VBA - 调整色线和 X 轴间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10879750/
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 - Adjust Color Line and X-Axis Interval
提问by Ivan Chan
I used Excel VBA to create a chart and I want to adjust the line color and x-axis interval.
我使用 Excel VBA 创建图表，我想调整线条颜色和 x 轴间隔。
Below is the code:
下面是代码：
Sub add_cpu_chart(server_hostname, site)
On Error Resume Next
Dim rpt_site As String
rpt_site = site
Set tbl = ThisWorkbook.Sheets(server_hostname).ListObjects(1)
strTableName = tbl.Name
strTableRange = tbl.Range.Address
Dim m_s_name, m_e_name As Date
m_s_name = CDate(fromDateStr)
m_e_name = CDate(toDateStr)
'Create new embedded chart
Set shp = ThisWorkbook.Sheets(server_hostname).Shapes.AddChart
'Position Shape over range
shp.Top = 100
shp.Left = 200
With shp
With .Chart
'Specify source data and orientation
'.SetSourceData Source:=ActiveSheets.Range(Table1 & "[#All]"), _
'PlotBy:=xlRows
.SetSourceData Source:=ThisWorkbook.Sheets(server_hostname).Range(strTableName & "[#All]")
.ChartType = xlLineMarkers
.SetElement (msoElementChartTitleAboveChart)
.HasTitle = True
.ChartTitle.Text = "CPU Utilization of " & server_hostname & " in " & _
rpt_site & " (" & Format(m_s_name, "dd-Mmm") & " to " & Format(m_e_name, "dd-Mmm yyyy") & ")"
.ChartTitle.Font.Size = 14
'.ChartArea.Font.Size = 14
.Legend.Delete
.SetElement (msoElementPrimaryValueAxisTitleRotated)
.Axes(xlValue, xlPrimary).AxisTitle.Text = "CPU Utlization (%)"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 10
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MinorUnit = 2
.Axes(xlValue).MaximumScale = 100
.Axes(xlValue).MajorUnit = 20
.Axes(xlValue).TickLabels.NumberFormat = "General"
.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Date of the Month"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 10
'.Axes(xlCategory).MajorUnit = 1
.Axes(xlCategory).TickLabels.NumberFormat = "d"
With .PlotArea.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.150000006
.Transparency = 0
.Solid
End With
End With
End With
End Sub
I want to change the line color to red - as in excel 2003 - as well as change the data in interval separated by 2 days. Currently, the data is in the form 1,2,3,4...I want to display the data in the form 1,3 ,5....
我想将线条颜色更改为红色 - 如在 excel 2003 中 - 以及更改间隔 2 天的数据。目前，数据的形式是1,2,3,4...我想在表格中显示数据1,3 ,5...。
回答by Jon Peltier
In a line chart, the X axis' minimumscale, maximum scale, majorunit, and minorunit are meaningless. The X values are merely categories which have no interpreted numerical values even if the labels show numerals.
在折线图中，X 轴的minimumscale、maximum scale、majorunit 和minorunit 没有意义。X 值仅仅是类别，即使标签显示数字，也没有解释的数值。
You should probably change to an XY chart, which will allow full control over the axis scale using minimumscale, maximum scale, majorunit, and minorunit. Of course, an XY chart allows the same formatting of series markers and lines as in a line chart, but the confusing terminology will live on forever.
您可能应该更改为 XY 图表，这将允许使用 minimumscale、maximum scale、majorunit 和 minorunit 完全控制轴刻度。当然，XY 图表允许使用与折线图相同的系列标记和线条格式，但令人困惑的术语将永远存在。
If for some reason you decide to stick with a line chart, you can use ActiveChart.Axes(xlCategory).TickLabelSpacing = 2
如果由于某种原因您决定坚持使用折线图，您可以使用 ActiveChart.Axes(xlCategory).TickLabelSpacing = 2
