SpecialCells（xlCellTypeVisible）在UDF中不起作用 [英] SpecialCells(xlCellTypeVisible) not working in UDF

问题描述

根据@Chips Ahoy提供的问题，我决定创建一个UDF来查找一个可见单元格的PercentRank

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

当我运行以下，两个地址输出读取相同。在我使用 = VisiblePercentRank（$ A $ 2：$ A $ 41,0.5）的公式的示例中，输出到直接窗口的两个地址都读取 $ A $ 2：$ A $ 41 ，尽管第3到11行被自动过滤器隐藏。

代码：函数VisiblePercentRank（x As Range，RankVal As Double）
Debug.Print x.Address，x.Rows.SpecialCells（ xlCellTypeVisible）.Address
VisiblePercentRank = WorksheetFunction.PercentRank（x.Rows.SpecialCells（xlCellTypeVisible），RankVal）
结束函数

另外尝试删除 .Rows ：

 函数VisiblePercentRank（x As Range，RankVal As Double）
 Debug.Print x.Address，x.SpecialCells（xlCellTypeVisible）.Address 
 VisiblePercentRank = WorksheetFunction.PercentRank（ x.SpecialCells（xlCellTypeVisible），RankVal）
结束函数
  

如果第二个输出未读 $ A $ 2，$ A $ 12：$ A $ 41 或者我错过了什么？

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

BRAIN FRYING UPDATE

我发现我的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 
  

解决方案

在UDFs中，似乎 SpecialCells 已知失败。 几个来源： 1 ， 2 ， 3

你必须创建自己的功能。可能是这样的：

 函数VisiblePercentRank（x As Range，RankVal As Double）
 Debug.Print x.Address ，VisibleCells（x）.Address 
 VisiblePercentRank = WorksheetFunction.PercentRank（VisibleCells（x），RankVal）
结束函数
 
私有函数VisibleCells（rng As Range）As Range 
 Dim r As Range 
对于每个r在rng 
如果r.EntireRow.Hidden = False然后
如果VisibleCells是Nothing然后
设置VisibleCells = r 
否则
 Set VisibleCells = Union（VisibleCells，r）
 End If 
 End If 
 Next r 
 End function 
  

Based on the question posed by @Chips Ahoy, I decided to create a UDF to find the PercentRank of visible cells in a range.

While @Chips seems happy with my syntax correction, I am actually unable to get my UDF to work correctly.

When I run the below, the two addresses output read identical. In my example using a formula of =VisiblePercentRank($A$2:$A$41,0.5) , both addresses output to the immediate window read $A$2:$A$41, despite rows 3 to 11 being hidden by an autofilter.

Code:

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

Also tried removing .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

Should the second output not read $A$2,$A$12:$A$41 or have I missed something?

Using Excel/Office 2013, 64bit on Win7, 64bit.

BRAIN FRYING UPDATE

I have found that my UDF works if I run it from the immediate window:

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

But if run from an in-cell formula of =VisiblePercentRank(A2:A41,0.5):

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

解决方案

It seems that SpecialCells is known to fail in UDFs. A few sources: 1, 2, 3

You'd have to create your own function. Perhaps something like this:

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

这篇关于SpecialCells（xlCellTypeVisible）在UDF中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！