Excel VBA中用Now()数组赋值XValues致图表异常的解决方法

问题背景 #

原运行正常的VBA代码如下：

Public Sub Macro1()

'Enter Today's date into A1 and A2 cells.
ActiveSheet.Range("A1").Value = Now()
ActiveSheet.Range("A2").Value = Now()

'Add a chart.
With ActiveSheet.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=200)
    .Name = "myChart"
End With

'Add a xlLine serie.
With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
    .ChartType = xlLine
    .Name = "Example"
    .Values = Array(100, 200, 300, 400, 500, 600)
    .XValues = Array(45658, 45689, 45717, 45748, 45778, 45809)
End With
    
End Sub

不想从Excel单元格提取图表数据，尝试将.XValues = ActiveSheet.Range("A1:A2")替换为.XValues = Array(Now(), Now())后，图表显示异常，需要解决此问题。

原因分析 #

Excel图表的XValues直接使用Now()生成的日期时间值时，VBA会将其当作普通数值而非日期类型处理，导致图表无法正确解析为时间轴。原代码中的45658这类数字是Excel的日期序列号（对应特定日期），图表能正常识别。

解决方法 #

方法1：用CDbl()转换Now()为日期序列号 #

将Now()转换为Excel认可的日期序列号，和原代码的数值格式保持一致：

.XValues = Array(CDbl(Now()), CDbl(Now()))

方法2：定义日期类型数组 #

先创建日期类型的数组，再赋值给XValues，确保图表识别为日期：

Dim xDates(1 To 2) As Date
xDates(1) = Now()
xDates(2) = Now()
.XValues = xDates

完整修正示例代码（方法1） #

Public Sub Macro1_Fixed()

'Add a chart.
With ActiveSheet.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=200)
    .Name = "myChart"
End With

'Add a xlLine serie with date XValues.
With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
    .ChartType = xlLine
    .Name = "Example"
    .Values = Array(100, 200) '匹配X值数量的数值
    .XValues = Array(CDbl(Now()), CDbl(Now() + 1)) '转换为日期序列号，+1演示不同日期
End With
    
End Sub

额外提示 #

如果需要添加多个日期点，只需在数组中补充CDbl(Now() + n)（n为天数）或具体日期的序列号，图表会自动识别为时间轴并正常显示。

内容的提问来源于stack exchange，提问作者Kram Kramer