我将撰写VB脚本来生成图表。在X轴上,我有日期,在Y轴上,有温度。
在X轴上,我想以“dd-mm”的格式显示时间。我的数据如下:
在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
这是我目前用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
很遗憾,当我生成图表时,整个日期值(例如2014-06-07 01:00)被应用于X轴。
您对如何修复我所拥有的内容有什么想法/建议?
更新:
创建图表的全部代码:
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
chtChart.Chart
是你要查看的对象吗?此外,你确定日期被识别为日期而非字符串吗?你可以这样调试它:1)尝试写入chtChart.Chart.Activate
看看是否弹出。2)第二个点,尝试写入A1-1
(假设一个日期在范围A1
中),观察是否会获得值错误。 - Matteo NNZ