如何在Excel VBA中将单元格区域导入变体数组并使用Sort方法排序？

为什么你的原有代码无法运行 #

• Range.Sort方法是直接修改工作表上的区域，它的返回值不是排序后的数组，而是空值，所以不能直接赋值给TU变量。
• 由于你的区域基于公式，直接用Sort会按公式内容排序，而不是公式计算后的值，这不符合你的需求。

两种可行的解决方案 #

方案1：利用临时内存工作表排序（推荐，无需自行编写排序逻辑） #

这种方法借助Excel自带的排序功能，把数组写入隐藏的临时工作表，排序后再读回数组，避免直接修改原区域，同时利用成熟的排序算法：

Sub SortRangeValuesToArray()
    Dim sortRange As Range
    Dim TU As Variant
    Dim tempWs As Worksheet
    
    ' 绑定目标命名区域
    Set sortRange = ThisWorkbook.Names("Thermal_Units").RefersToRange
    
    ' 将区域的计算值读取到二维数组
    TU = sortRange.Value
    
    ' 创建隐藏的临时工作表
    Set tempWs = ThisWorkbook.Worksheets.Add
    tempWs.Visible = xlSheetHidden
    
    ' 把数组写入临时工作表
    tempWs.Range("A1").Resize(UBound(TU, 1), UBound(TU, 2)).Value = TU
    
    ' 在临时表上按第28列升序排序（此时是值排序，无公式干扰）
    tempWs.Range("A1").Resize(UBound(TU, 1), UBound(TU, 2)).Sort _
        Key1:=tempWs.Columns(28), _
        Order1:=xlAscending, _
        Header:=xlNo
    
    ' 将排序后的值读回数组
    TU = tempWs.Range("A1").Resize(UBound(TU, 1), UBound(TU, 2)).Value
    
    ' 清理临时工作表
    Application.DisplayAlerts = False
    tempWs.Delete
    Application.DisplayAlerts = True
    
    ' 这里可添加数组的后续使用逻辑，例如写入指定区域
    ' Range("YourOutputRange").Value = TU
End Sub

方案2：自行编写数组排序算法（适合小数据量） #

如果不想依赖临时工作表，可以手动实现排序逻辑，比如针对二维数组的冒泡排序（示例为按第28列升序）：

Sub SortArrayManually()
    Dim sortRange As Range
    Dim TU As Variant
    Dim i As Long, j As Long
    Dim tempRow As Variant
    
    ' 读取区域值到数组
    Set sortRange = ThisWorkbook.Names("Thermal_Units").RefersToRange
    TU = sortRange.Value
    
    ' 冒泡排序：按第28列的值升序排列整行
    For i = LBound(TU, 1) To UBound(TU, 1) - 1
        For j = i + 1 To UBound(TU, 1)
            If TU(i, 28) > TU(j, 28) Then
                ' 交换两行数据
                tempRow = TU(i, :)
                TU(i, :) = TU(j, :)
                TU(j, :) = tempRow
            End If
        Next j
    Next i
    
    ' 后续使用数组，例如输出到工作表
    ' Range("YourOutputRange").Value = TU
End Sub

方案选择建议 #

• 如果数据量较大（千行以上）或需要多关键字排序，优先用方案1，Excel内置排序的效率远高于手动实现的简单算法。
• 如果数据量小，或不想创建临时工作表，可使用方案2，也可以把冒泡排序替换为更高效的快速排序算法。

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