Excel-VBA自定义函数 将结果返回一个数组

应用场景

将函数结果返回一个数组

知识要点

1：用Collection集合去除重复值

2：Transpose转置后的数组返回给函数

Function Only(Rng As Range)

On Error Resume Next "防错

Dim Onlys As New Collection "声明集合

Dim Arr() As String, Cell As Range, i As Long

For Each Cell In Rng "遍历区域

If Len(Cell) > 0 Then "如果不是空的单元格

i = i 1

Onlys.Add Cell.Text, CStr(Cell.Text)

End If

Next

ReDim Arr(1 To i) "重新声明数组变量的存储控件

For i = 1 To Onlys.Count

Arr(i) = Onlys.item(i) "将集合中的所有值存入数组

Next

Only = WorksheetFunction.Transpose(Arr) "将数组转置后的值给函数

End Function