vba excel按单元格值对组合框项进行排序，并自动选择相应项

Private Sub ComboBox1_Change()
    Dim rng As Range
    Dim cell As Range
    Dim items() As Variant
    Dim i As Long
    
    ' 获取要排序的单元格范围
    Set rng = Range("A1:A10")
    
    ' 将单元格值存储到数组中
    ReDim items(rng.Cells.Count - 1)
    i = 0
    For Each cell In rng
        items(i) = cell.Value
        i = i + 1
    Next cell
    
    ' 对数组进行排序
    Call QuickSort(items, LBound(items), UBound(items))
    
    ' 清空组合框的项
    ComboBox1.Clear
    
    ' 将排序后的项添加到组合框中
    For i = LBound(items) To UBound(items)
        ComboBox1.AddItem items(i)
    Next i
    
    ' 自动选择相应的项
    ComboBox1.Value = ActiveCell.Value
End Sub

Sub QuickSort(arr() As Variant, ByVal left As Long, ByVal right As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Variant
    Dim temp As Variant
    
    i = left
    j = right
    pivot = arr((left + right) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If left < j Then
        Call QuickSort(arr, left, j)
    End If
    
    If i < right Then
        Call QuickSort(arr, i, right)
    End If
End Sub