VBA如何按参数值从大到小排序并输出对应的参数名称

你可以通过「字典存储变量名与对应值 + 自定义排序」的逻辑实现需求，完整代码如下：

' 1. 创建字典存储变量名和对应数值
Dim sortDict As Object
Set sortDict = CreateObject("Scripting.Dictionary")
sortDict.Add "Chicago_SantaFe", Chicago_SantaFe
sortDict.Add "Vancouver_SantaFe", Vancouver_SantaFe
sortDict.Add "Calgary_SaltLakeCity", Calgary_SaltLakeCity

' 2. 将字典内容转成二维数组，方便排序
Dim sortArr() As Variant
ReDim sortArr(1 To sortDict.Count, 1 To 2)
Dim index As Integer: index = 1
Dim key As Variant
For Each key In sortDict.Keys
    sortArr(index, 1) = key
    sortArr(index, 2) = sortDict(key)
    index = index + 1
Next

' 3. 冒泡排序：按数值降序排列，同步交换对应变量名
Dim i As Integer, j As Integer, temp As Variant
For i = LBound(sortArr, 1) To UBound(sortArr, 1) - 1
    For j = i + 1 To UBound(sortArr, 1)
        If sortArr(i, 2) < sortArr(j, 2) Then
            ' 交换数值
            temp = sortArr(j, 2)
            sortArr(j, 2) = sortArr(i, 2)
            sortArr(i, 2) = temp
            ' 交换对应的变量名
            temp = sortArr(j, 1)
            sortArr(j, 1) = sortArr(i, 1)
            sortArr(i, 1) = temp
        End If
    Next j
Next i

' 4. 拼接输出结果，赋值给标签
Dim outputStr As String: outputStr = ""
For i = LBound(sortArr, 1) To UBound(sortArr, 1)
    outputStr = outputStr & sortArr(i, 1) & vbCrLf
Next
Result_Label.Caption = outputStr

• 要保证标签可以正常显示多行内容，需要提前将Result_Label的WordWrap属性设为True，AutoSize属性按需调整
• 如果后续需要加入更多排序参数，只需在字典中新增对应的「变量名-变量值」键值对即可，无需修改排序逻辑
• 该示例用冒泡排序实现，仅适合少量参数排序；如果参数数量超过10个，可以替换为快速排序提升效率

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