日期未正确显示在折线图的X轴上

Question

我有一个2d数组，其中第一个数组是日期列表。日期的格式为#2020-05-18#，共有35个日期。

当构建一个以日期为X轴的图时，它们不会显示为日期，而是数字1-35。

如果我加上.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "YYYY-MM-DD"行，我得到的日期是完全错误的。它们从1900-01-01开始，然后从1900-01-02开始，一直到1900-02-04。我试着把数组改成长的、双的、日期的，但无济于事。

有人知道怎么了吗？

这是代码：

Set myChart = Graf.Shapes.AddChart(Left:=20, Top:=53, Width:=1000, Height:=400)
With myChart.Chart
For i = 1 To UBound(AllDataLists, 2) 'AllDataLists is the 2d array. The first array contains the 
'dates, the rest contains the data.
ReDim ActualData(1 To UBound(AllDataLists, 1)) As Date
For j = 1 To UBound(AllDataLists, 1)
ActualData(j) = AllDataLists(j, i)
Next j
.SeriesCollection.NewSeries
.SeriesCollection(i).Values = ActualData
.SeriesCollection(i).ChartType = xlLine
.SeriesCollection(i).AxisGroup = 1
Next i
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "YYYY-MM-DD"
End With

不管怎样我都解决了！！！这就是解决方案：

Set myChart = Graf.Shapes.AddChart(Left:=20, Top:=53, Width:=1000, Height:=400)
With myChart.Chart
For i = 1 To UBound(AllDataLists, 2)
If i = 1 Then
ReDim actualdate(1 To UBound(AllDataLists, 1)) As Date
For j = 1 To UBound(AllDataLists, 1)
actualdate(j) = AllDataLists(j, i)
Next j
Else
ReDim actualdata(1 To UBound(AllDataLists, 1)) As Integer
For j = 1 To UBound(AllDataLists, 1)
actualdata(j) = AllDataLists(j, i)
Next j
End If
.SeriesCollection.NewSeries
If i = 1 Then
.SeriesCollection(i).XValues = actualdate
Else

.SeriesCollection(i).Values = actualdata
.SeriesCollection(i).ChartType = xlLine
.SeriesCollection(i).Name = NewDataLabel(i - 1)
End If


.SeriesCollection(i).AxisGroup = 1


Next i
End With

基本上，我制作了两个数组，一个包含日期，另一个包含数据。然后我使用了带有日期的数组作为XValues，而不是Values。

Answer 1

我应该早点重新加载页面；-(

Sub AddChart()
Dim myChart
Set myChart = ActiveSheet.Shapes.AddChart(Left:=20, Top:=53, Width:=1000, Height:=400)

With myChart.Chart
.SeriesCollection.NewSeries
.ChartType = xlColumnClustered
.SeriesCollection(1).XValues = "{43831,43862,43891,43922,43952}"
.SeriesCollection(1).Values = "{-10,10,30,50,70}"
.Axes(xlCategory).TickLabels.NumberFormat = "DD.MM.YYYY"
End With
End Sub