vba - Excel UDF 筛选范围

Question

我有一个函数，它接受一系列值作为输入(只是一列)以及一些阈值。我想返回一个经过过滤的范围，以包含原始范围中大于阈值的所有值。我有以下代码:

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Range

Dim Cell As Range
Dim ResultRange As Range

For Each Cell In Rng
    If Abs(Cell.Value) >= Limit Then
        If ResultRange Is Nothing Then
            Set ResultRange = Cell
        Else
            Set ResultRange = Union(ResultRange, Cell)
        End If
    End If    
Next
Set FilterGreaterThan = ResultRange
End Function

问题是，一旦某个数字低于阈值，该数字之后高于阈值的其他数字就不会添加到该范围中。

例如:

Threshold - 2

Numbers -

3
4
1
5

它将循环添加 3 和 4，但不会添加 5。我最终收到#value 错误。但我没有收到错误，如果我只输入范围 - 3, 4 或范围 - 3, 4, 1，它就可以正常工作。

Answer 1

看起来 UDF 不喜欢将非连续范围写回数组。

解决这个问题的一种方法是重写 UDF，如下所示。它假设输出数组仅在列中，但允许多列输入。

Option Explicit

Public Function FilterGreaterThan(Rng As Range, Limit As Double) As Variant

Dim Cell As Range
Dim WriteArray() As Variant
Dim i As Long
Dim cellVal As Variant
Dim CountLimit As Long

CountLimit = WorksheetFunction.CountIf(Rng, ">=" & Limit)
ReDim WriteArray(1 To CountLimit, 1 To 1) 'change if more than 1 column
For Each Cell In Rng

    cellVal = Cell.Value
    If Abs(cellVal) >= Limit Then
            i = i + 1 'change if more than 1 column
            WriteArray(i, 1) = cellVal 'change if more than 1 column
    End If
Next
FilterGreaterThan = WriteArray
End Function