Excel VBA更新图表后X轴日期格式异常的技术问询

我原本用VBA创建了一个图表，把X轴格式设置为d-mmm，但运行下面的Update_graph()函数添加数据后，X轴格式自动变成了m-dd-yy。手动修改图表格式或者在函数里重新设置都没用，图表效果如下：

我的Update_graph()代码：

Sub Update_graph()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")


    Dim row As Long
    Dim currentDate As Date
    Dim increase As Double
    
    Dim chartSeries As Series
    Set chartSeries = chartObj.chart.SeriesCollection(1)
    
    
    Dim dataPoints() As Double
    Dim xValues() As Variant
    Dim dataPointCount As Long


    For row = 4 To 8
    
        
        currentDate = Format(ws.Cells(row, 1).Value, "d-mmm")
        increase = CalculatePercentageIncrease(ws, row) * 100
        
        ReDim Preserve dataPoints(1 To dataPointCount + 1)
        ReDim Preserve xValues(1 To dataPointCount + 1)
        dataPoints(dataPointCount + 1) = increase
        xValues(dataPointCount + 1) = currentDate
        
        dataPointCount = dataPointCount + 1
        
        
    Next row
    
    
    chartSeries.Values = dataPoints
    chartSeries.xValues = xValues


End Sub

解决方法 #

问题出在你用Format(ws.Cells(row, 1).Value, "d-mmm")把日期转成了字符串，图表处理这类数据时会自动识别并套用默认日期格式，手动修改的格式会被覆盖。正确做法是保留日期原始值，更新数据后再强制设置X轴格式：

修改后的代码：

Sub Update_graph()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim row As Long
    Dim currentDate As Date
    Dim increase As Double
    
    Dim chartSeries As Series
    Set chartSeries = chartObj.chart.SeriesCollection(1)
    
    Dim dataPoints() As Double
    Dim xValues() As Date ' 改为Date类型数组，保留原始日期值
    Dim dataPointCount As Long

    For row = 4 To 8
        ' 直接读取单元格的原始日期值，不要用Format转换为字符串
        currentDate = ws.Cells(row, 1).Value
        increase = CalculatePercentageIncrease(ws, row) * 100
        
        ReDim Preserve dataPoints(1 To dataPointCount + 1)
        ReDim Preserve xValues(1 To dataPointCount + 1)
        dataPoints(dataPointCount + 1) = increase
        xValues(dataPointCount + 1) = currentDate
        
        dataPointCount = dataPointCount + 1
    Next row
    
    chartSeries.Values = dataPoints
    chartSeries.xValues = xValues
    
    ' 更新数据后，强制设置X轴标签格式为d-mmm
    With chartObj.chart.Axes(xlCategory)
        .TickLabels.NumberFormat = "d-mmm"
    End With
End Sub

关键说明 #

1. 不要用Format()转换日期：保留日期的原始数值类型，图表才能正确识别为日期数据，而非文本或自动转换的格式。
2. 手动设置X轴格式：在更新完数据后，通过代码强制指定X轴标签的格式，确保格式不会被自动覆盖。

内容的提问来源于stack exchange，提问作者Angus Todd