VBA Modify Excel COUNTIFS UDF

Question

我对VBA很陌生我有一个Excel范围的单元格将被不断地自动筛选，而一个COUNTIFS UDF将只考虑可见（筛选）的单元格，这将对我有很大帮助。我发现这段代码似乎正是我所需要的，但它只适用于COUNTIF，而不适用于COUTIFS如果有人对如何修改它以使其计数有任何想法，那将是非常棒的！

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
    Dim Cell As Range
    Application.Volatile
    Set Vis = Nothing
    For Each Cell In Rin
        If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
            If Vis Is Nothing Then
                Set Vis = Cell
            Else
                Set Vis = Union(Vis, Cell)
            End If
        End If
     Next Cell
End Function
Function COUNTIFv(Rin As Range, Condition As Variant) As Long
'Same as Excel COUNTIF worksheet function, except does not count
'cells that are hidden
    Dim A As Range
    Dim Csum As Long
    Csum = 0
    For Each A In Vis(Rin).Areas
       Csum = Csum + WorksheetFunction.CountIf(A, Condition)
    Next A
    COUNTIFv = Csum
End Function

Answer 1

你可以通过这样做来实现。。。

考虑到您有不同的范围在不同的条件下进行测试：

Function COUNTIFSv(Rin1 As Range, Condition1 As Range, Optional Rin2 As Range, Optional Condition2 As Range, Optional Rin3 As Range, Optional Condition3 As Range) As Long
    'Same as Excel COUNTIF worksheet function, except does not count
    'cells that are hidden
    Dim Csum As Long
    Csum = 0
    If Not IsMissing(Rin3) AND Not IsMissing(Condition3) Then
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin1),Condition1,Vis(Rin2),Condition2,Vis(Rin3),Condition3)
    ElseIf Not IsMissing(Rin2) AND Not IsMissing(Condition2) Then
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin1),Condition1,Vis(Rin2),Condition2)
    Else
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin1),Condition1)
    End If
    COUNTIFSv = Csum
End Function

或者你可以在不同的条件下测试相同的范围：

Function COUNTIFSv(Rin As Range, Condition1 As Range, Optional Condition2 As Range, Optional Condition3 As Range) As Long
    Dim Csum As Long
    Csum = 0
    If Not IsMissing(Condition3) Then
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin),Condition1,Vis(Rin),Condition2,Vis(Rin),Condition3)
    ElseIf Not IsMissing(Condition2) Then
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin),Condition1,Vis(Rin),Condition2)
    Else
        Csum = Csum + WorksheetFunction.CountIfs(Vis(Rin),Condition1)
    End If
    COUNTIFSv = Csum
End Function