图表轴标签格式 vba 设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30403073/
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
chart axis label format vba settings
提问by JosiP
I'm writing vb script to generate charts. On the X axis, I have have the date and on the Y axis, the temp.
我正在编写 vb 脚本来生成图表。在 X 轴上，我有日期，在 Y 轴上，我有温度。
On the X axis I want to present time with the format "dd-mm". My data looks like this:
在 X 轴上，我想以“dd-mm”格式显示时间。我的数据如下所示：
2014-06-17 01:00
2014-06-17 02:00
2014-06-17 03:00
2014-06-17 04:00
2014-06-17 05:00
2014-06-17 06:00
2014-06-17 07:00
2014-06-17 08:00
2014-06-17 09:00
And this is what I have written in vb script so far:
到目前为止，这就是我在 vb 脚本中编写的内容：
With chtChart.Chart
.HasTitle = True
.ChartTitle.Text = sheetName & vbCr & "2014"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
End With
Unfortunately, when I generate the chart, the entire date value (e.g. 2014-06-07 01:00) is being applied to the X axis.
不幸的是，当我生成图表时，整个日期值（例如 2014-06-07 01:00）被应用于 X 轴。
Any thoughts/ideas on how I can fix what I have?
关于如何修复我所拥有的任何想法/想法？
Update:
更新：
whole code to create charts:
创建图表的完整代码：
Function AddChartSheet(sheetName As String, title As String) As Boolean
Dim ws As Worksheet
Dim chtChart As ChartObject
Dim measDataSheetName As String
'Create a new chart.
measDataSheetName = sheetName & "_measurements.csv"
Dim Lastrow As Integer
Dim seriesNames() As String
ActiveWorkbook.Sheets.Add.name = sheetName & " chart"
Set ws = ActiveWorkbook.Sheets(sheetName & " chart")
Set chtChart = ActiveSheet.ChartObjects.Add(Left:=25, Top:=25, _
Width:=700, Height:=500)
With chtChart
.name = sheetName
End With
Lastrow = ActiveWorkbook.Sheets(measDataSheetName).Cells(ActiveWorkbook.Sheets(measDataSheetName).Rows.Count, "P").End(xlUp).Ro
With chtChart.Chart
.HasTitle = True
.ChartTitle.Text = sheetName & vbCr & "2014"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
End With
With chtChart.Chart.SeriesCollection.NewSeries
.name = "Supply"
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
.Values = Worksheets(measDataSheetName).Range("T2:T" & Lastrow)
End With
With chtChart.Chart.SeriesCollection.NewSeries
.name = "Return"
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
.Values = Worksheets(measDataSheetName).Range("U2:U" & Lastrow)
End With
AddChartSheet = True
End Function
回答by JosiP
Ok i did find solution: add formatting after sending data:
好的，我确实找到了解决方案：发送数据后添加格式：
With chtChart.Chart
.HasTitle = True
.ChartTitle.Text = sheetName & vbCr & "2014"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = True
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature [C]"
End With
With chtChart.Chart.SeriesCollection.NewSeries
.name = "Supply"
.ChartType = xlXYScatterSmoothNoMarkers
.XValues = Worksheets(measDataSheetName).Range("P2:P" & Lastrow) '. SelectRange("C3", Range("C3").End(xlDown))
.Values = Worksheets(measDataSheetName).Range("T2:T" & Lastrow)
End With
With chtChart.Chart
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd-mm"
End With
and it worked out.
它成功了。
