Excel VBA动态生成图表时XValues时间值对齐异常问题

• 需求为遍历大批量数据集动态生成图表，数据集中每列数值均与特定时间值一一对应。其中Type列的单元格取值包含：空值、"Log "、"Log+"、"SRst"、"ARst"、"WRst"，需要将这些事件标记与用户指定时间范围内的其他自定义列数据共同绘制到图表中。
• 已完成图表基础绘制逻辑，但存在X轴值对齐异常：无论数据点对应的实际时间值为多少，数据点都会按照对应Type值的出现顺序，从第一个索引时间值开始依次排布，即第n次出现的某类Type值，会对齐第n个索引位的时间值，而非其实际对应的时间。

' This entire section is in a With block for the chart in another sub with the sub check in it's own sub
' I just felt like it's extra unnecessary stuff to add
Dim c4X As Range
Dim c5X As Range
Dim c6X As Range
Dim c7X As Range
Dim c8X As Range

' yFollow is the column that the events (c4-8X) are plotted on the Y axis with
' sh is the worksheet
For Each c In sh.Range(evChar & sIndex & ":" & evChar & eIndex)
    If c.Value = "Log " Then
        Call check(c.Row, yFollow, sh, c4X)
    ElseIf c.Value = "Log+" Then
        Call check(c.Row, yFollow, sh, c5X)
    ElseIf c.Value = "SRst" Then
        Call check(c.Row, yFollow, sh, c6X)
    ElseIf c.Value = "ARst" Then
        Call check(c.Row, yFollow, sh, c7X)
    ElseIf c.Value = "WRst" Then
        Call check(c.Row, yFollow, sh, c8X)
    End If
Next c

' I'm aware this is a poor way, just make a function. I will change it after I figure this out
If Not c4X Is Nothing Then
    .SeriesCollection(i).XValues = c4X
End If
If Not c5X Is Nothing Then
    .SeriesCollection(i + 1).XValues = c5X
End If
If Not c6X Is Nothing Then
    .SeriesCollection(i + 2).XValues = c6X
End If
If Not c7X Is Nothing Then
    .SeriesCollection(i + 3).XValues = c7X
End If
If Not c8X Is Nothing Then
    .SeriesCollection(i + 4).XValues = c8X
End If

Sub check(ByVal c As Long, ByVal col As String, ByVal sh As Worksheet, ByRef xRng As Range)
    If xRng Is Nothing Then
        Set xRng = sh.Range("A" & c)
    Else
        Set xRng = Union(xRng, sh.Range("A" & c))
    End If
End Sub

• 鼠标悬浮在图表数据点上时，提示框显示的时间值正确，例如ARst类型的点位悬浮提示显示对应时间为12:40:21，但该点在图表上实际绘制在12:40:00的起始位置。
• 右键点击图表选择「选择数据」查看系列配置时，该系列的X值范围显示为12:40:00-12:40:02，与赋值的实际时间值不符。
• 核心矛盾：给XValues赋值后，点位悬浮提示的取值正确，但实际绘图位置无法匹配对应的时间值。

核心bug来自Union生成的非连续单元格区域的图表赋值逻辑：Excel图表系列接收非连续Range作为XValues数据源时，不会按每个单元格存储的实际值定位坐标，而是根据区域包含的单元格总数量，生成从系列X轴起点开始的连续等距排布位置；仅在触发悬浮提示时，才会读取源单元格的实际值展示，因此会出现提示信息正确、绘图位置完全错位的现象。

弃用Union合并Range后直接给系列赋值的写法，改为遍历数据时将每个点对应的X轴时间值、Y轴数值存入一维数组，最终将数组赋值给系列的XValues和Values属性，即可跳过Excel对非连续Range的自动索引映射逻辑，保证点位坐标和实际值完全对应。
修复后的核心实现代码如下：

' 替换原Range类型声明，使用数组存储各系列的X/Y坐标值
Dim c4X() As Variant, c4Y() As Variant
Dim c5X() As Variant, c5Y() As Variant
Dim c6X() As Variant, c6Y() As Variant
Dim c7X() As Variant, c7Y() As Variant
Dim c8X() As Variant, c8Y() As Variant
' 各系列的数据点计数指针
Dim p4 As Long, p5 As Long, p6 As Long, p7 As Long, p8 As Long
p4 = 0: p5 = 0: p6 = 0: p7 = 0: p8 = 0

' 遍历数据行时同步收集对应点的时间值、Y轴数值到数组
For Each c In sh.Range(evChar & sIndex & ":" & evChar & eIndex)
    tVal = sh.Range("A" & c.Row).Value ' 读取当前行对应的时间值
    yVal = sh.Range(yFollow & c.Row).Value ' 读取当前行对应的Y轴数值
    Select Case c.Value
        Case "Log "
            ReDim Preserve c4X(0 To p4)
            ReDim Preserve c4Y(0 To p4)
            c4X(p4) = tVal
            c4Y(p4) = yVal
            p4 = p4 + 1
        Case "Log+"
            ReDim Preserve c5X(0 To p5)
            ReDim Preserve c5Y(0 To p5)
            c5X(p5) = tVal
            c5Y(p5) = yVal
            p5 = p5 + 1
        Case "SRst"
            ReDim Preserve c6X(0 To p6)
            ReDim Preserve c6Y(0 To p6)
            c6X(p6) = tVal
            c6Y(p6) = yVal
            p6 = p6 + 1
        Case "ARst"
            ReDim Preserve c7X(0 To p7)
            ReDim Preserve c7Y(0 To p7)
            c7X(p7) = tVal
            c7Y(p7) = yVal
            p7 = p7 + 1
        Case "WRst"
            ReDim Preserve c8X(0 To p8)
            ReDim Preserve c8Y(0 To p8)
            c8X(p8) = tVal
            c8Y(p8) = yVal
            p8 = p8 + 1
    End Select
Next c

' 将数组直接赋值给图表系列，规避非连续Range的坐标映射问题
If p4 > 0 Then
    .SeriesCollection(i).XValues = c4X
    .SeriesCollection(i).Values = c4Y
End If
If p5 > 0 Then
    .SeriesCollection(i + 1).XValues = c5X
    .SeriesCollection(i + 1).Values = c5Y
End If
If p6 > 0 Then
    .SeriesCollection(i + 2).XValues = c6X
    .SeriesCollection(i + 2).Values = c6Y
End If
If p7 > 0 Then
    .SeriesCollection(i + 3).XValues = c7X
    .SeriesCollection(i + 3).Values = c7Y
End If
If p8 > 0 Then
    .SeriesCollection(i + 4).XValues = c8X
    .SeriesCollection(i + 4).Values = c8Y
End If

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