使用数组数据创建xlXYScatter类Excel图表无显示线条问题求助

以下是针对你遇到的问题的具体排查和解决方案：

核心问题排查方向 #

你的代码无法显示线条，大概率是数组维度不匹配或日期数据类型错误导致的，XY散点图对输入数据的格式要求很严格。

1. 确保数组是一维格式 #

从工作表区域直接获取的数组默认是二维数组（即使是单列数据，格式也是Arr(行号, 列号)），但图表的XValues和Values属性需要一维数组。你需要先把二维数组转成一维：

' 把二维数组转成一维的工具函数
Function To1DArray(arr As Variant) As Variant
    Dim tempArr() As Variant
    ReDim tempArr(1 To UBound(arr, 1))
    
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        tempArr(i) = arr(i, 1)
    Next i
    
    To1DArray = tempArr
End Function

修改你的数据获取代码：

' 先转成一维数组再赋值
TempValuesAWO = To1DArray(GetTempValues(TempRngAWO))
TempDateAWO = To1DArray(GetDateValues(TempRngAWO))

2. 确保日期是Date类型而非字符串 #

如果GetDateValues返回的是字符串格式的日期，图表会把它当作文本处理，无法映射到数值型的X轴（Excel中日期本质是序列号）。需要把字符串转成Date类型：

' 字符串数组转Date类型数组的工具函数
Function ToDateArray(strArr As Variant) As Variant
    Dim tempArr() As Date
    ReDim tempArr(1 To UBound(strArr))
    
    Dim i As Long
    For i = 1 To UBound(strArr)
        tempArr(i) = CDate(strArr(i))
    Next i
    
    ToDateArray = tempArr
End Function

更新日期数组的赋值：

TempDateAWO = ToDateArray(To1DArray(GetDateValues(TempRngAWO)))

3. 调整图表初始化和系列设置顺序 #

可以先添加系列再设置图表类型，避免默认系列类型冲突，同时确保图表对象初始化正确：

' 先创建新的图表工作表
Dim Xhart As Chart
Set Xhart = Charts.Add
Xhart.Location Where:=xlLocationAsNewSheet, Name:="温度记录图表"

With Xhart
    .HasTitle = True
    .ChartTitle.Text = "Rejestracja temperatury"
    .Legend.Position = xlLegendPositionBottom
    
    ' 先添加系列
    Dim newSeries As Series
    Set newSeries = .SeriesCollection.NewSeries
    newSeries.Name = "AWO"
    newSeries.XValues = TempDateAWO
    newSeries.Values = TempValuesAWO
    
    ' 再设置图表类型和轴属性
    .ChartType = xlXYScatterLinesNoMarkers
    With .Axes(xlCategory)
        .MaximumScale = DateTimeStop
        .MinimumScale = DateTimeStart
        .TickLabels.NumberFormat = "DD.MM.YYYY hh:mm:ss"
    End With
End With

完整可运行示例 #

把上述逻辑整合后的完整代码：

Sub CreateCombinedXYChart()
    Dim Xhart As Chart
    Dim TempRngAWO As Range
    Dim TempValuesAWO As Variant, TempDateAWO As Variant
    Dim DateTimeStart As Date, DateTimeStop As Date
    
    ' 替换成你的实际数据区域和时间范围
    Set TempRngAWO = Sheets("数据源").Range("A2:A50")
    DateTimeStart = CDate("2023-02-09 11:20:00")
    DateTimeStop = CDate("2023-02-09 11:30:00")
    
    ' 处理数据数组
    TempValuesAWO = To1DArray(GetTempValues(TempRngAWO))
    TempDateAWO = ToDateArray(To1DArray(GetDateValues(TempRngAWO)))
    
    ' 创建图表
    Set Xhart = Charts.Add
    Xhart.Location Where:=xlLocationAsNewSheet, Name:="温度记录图表"
    
    With Xhart
        .HasTitle = True
        .ChartTitle.Text = "Rejestracja temperatury"
        .Legend.Position = xlLegendPositionBottom
        
        ' 添加AWO系列
        Dim newSeries As Series
        Set newSeries = .SeriesCollection.NewSeries
        newSeries.Name = "AWO"
        newSeries.XValues = TempDateAWO
        newSeries.Values = TempValuesAWO
        
        ' 设置图表样式和轴参数
        .ChartType = xlXYScatterLinesNoMarkers
        With .Axes(xlCategory)
            .MaximumScale = DateTimeStop
            .MinimumScale = DateTimeStart
            .TickLabels.NumberFormat = "DD.MM.YYYY hh:mm:ss"
        End With
    End With
End Sub

' 二维数组转一维
Function To1DArray(arr As Variant) As Variant
    Dim tempArr() As Variant
    ReDim tempArr(1 To UBound(arr, 1))
    
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        tempArr(i) = arr(i, 1)
    Next i
    
    To1DArray = tempArr
End Function

' 字符串转Date数组
Function ToDateArray(strArr As Variant) As Variant
    Dim tempArr() As Date
    ReDim tempArr(1 To UBound(strArr))
    
    Dim i As Long
    For i = 1 To UBound(strArr)
        tempArr(i) = CDate(strArr(i))
    Next i
    
    ToDateArray = tempArr
End Function

' 替换成你实际的GetTempValues逻辑
Function GetTempValues(rng As Range) As Variant
    GetTempValues = rng.Offset(0, 1).Value ' 假设温度在日期列的右侧
End Function

' 替换成你实际的GetDateValues逻辑
Function GetDateValues(rng As Range) As Variant
    GetDateValues = rng.Value
End Function

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