vba - SpecialCells(xlCellTypeVisible) 在 UDF 中不起作用

Question

基于question由 @Chips Ahoy 提出，我决定创建一个 UDF 来查找某个范围内可见单元格的 PercentRank。

虽然 @Chips 似乎对我的语法修正感到满意，但实际上我无法让我的 UDF 正常工作。

当我运行下面的命令时，两个地址输出读取相同。在我的示例中，使用公式 =VisiblePercentRank($A$2:$A$41,0.5) ，输出到立即窗口的两个地址均为 $A$2:$A$41 ，尽管第 3 行到第 11 行被自动筛选器隐藏。

代码:

Function VisiblePercentRank(x As Range, RankVal As Double)
    Debug.Print x.Address, x.Rows.SpecialCells(xlCellTypeVisible).Address
    VisiblePercentRank = WorksheetFunction.PercentRank(x.Rows.SpecialCells(xlCellTypeVisible), RankVal)
End Function

还尝试删除.Rows:

Function VisiblePercentRank(x As Range, RankVal As Double)
    Debug.Print x.Address, x.SpecialCells(xlCellTypeVisible).Address
    VisiblePercentRank = WorksheetFunction.PercentRank(x.SpecialCells(xlCellTypeVisible), RankVal)
End Function

第二个输出应该不读取$A$2,$A$12:$A$41还是我错过了什么？

在 Win7 64 位上使用 Excel/Office 2013，64 位。

大脑煎炸更新

我发现如果我从直接窗口运行我的 UDF，它就可以工作:

?VisiblePercentRank(range("A2:A41"),0.5)
$A$2:$A$41    $A$2:$A$11,$A$39:$A$41
 0.207 

但如果从单元格内公式 =VisiblePercentRank(A2:A41,0.5) 运行:

$A$2:$A$41    $A$2:$A$41

Answer 1

似乎已知 SpecialCells 在 UDF 中会失败。 一些来源:1 , 2 , 3

您必须创建自己的函数。也许是这样的:

Function VisiblePercentRank(x As Range, RankVal As Double)
    Debug.Print x.Address, VisibleCells(x).Address
    VisiblePercentRank = WorksheetFunction.PercentRank(VisibleCells(x), RankVal)
End Function

Private Function VisibleCells(rng As Range) As Range
    Dim r As Range
    For Each r In rng
        If r.EntireRow.Hidden = False Then
            If VisibleCells Is Nothing Then
                Set VisibleCells = r
            Else
                Set VisibleCells = Union(VisibleCells, r)
            End If
        End If
    Next r
End Function