VBA-排序范围变量传递到函数

Question

我创建了一个VBA UDF，该VBA UDF在Excel电子表格中接收由用户选择的范围。例如， = myFunction（a1：a5）。

我的问题是：我可以在此范围内对单元的值进行分类，而无需在每个单元格上循环然后重新创建变量？

我尝试了下面的方法，但它不起作用：

MyVariableName.Sort 1, xlAscending

预先感谢

Wendell

Answer 1

假设只选择了一个列，您可以将这些值放入数组中，然后泡泡排序数组。

Function MyUDF(rng as range)
Dim vals() as variant
redim vals(rng.rows.count - 1)
for each cell in rng
   vals(x) = cell.value
   x = x + 1
next cell
'your code
End Function

---

Sub BubbleSort(list() As Variant)
'   Sorts an array using bubble sort algorithm
Dim first As Integer, last As Long
Dim i As Long, j As Long
Dim temp As Long
first = LBound(list)
last = UBound(list)
For i = first To last - 1
    For j = i + 1 To last
        If list(i) > list(j) Then
            temp = list(j)
            list(j) = list(i)
            list(i) = temp
        End If
    Next j
Next i
End Sub

Vals数组将被排序。