在 VBA 中使用字典(哈希)

Question

• 我有一个包含多个工作表的 excel 工作簿。

我

• 必须使用过滤器获取某些条目的计数（我在这里搜索文本而不是使用过滤器）

• "主"表是更新计数的位置。从工作簿中的其他工作表中搜索字符串

• 应更新计数的单元格会有所不同。

• 搜索条件，关键字，工作表，范围等在我发布的示例代码中给出。

代码示例：

在单元格AE43中，仅当工作表"TT"满足上述标准时，计数才会更新。

因此，同样，我必须对不同的单元格使用相同的代码 30+ 次才能获取数据。

因此，我想知道我们是否可以在此处使用"字典"功能（其他语言的哈希），以便单元格在满足条件时可以自动更新，而不是键入类似搜索的代码。

Sub WBR()
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
With ActiveWorkbook.Worksheets("TT")                'no of tickets processed - summary
[AE43] = wf.CountIfs(.Range("I:I"), "<>Duplicate TT", _
                  .Range("G:G"), "<>Not Tested", _
                  .Range("U:U"), "Item")
 End With
With ActiveWorkbook.Worksheets("TT")                'not tested tickets - summary
[AE44] = wf.CountIfs(.Range("G:G"), "Not Tested")
End With
With ActiveWorkbook.Worksheets("TT")                'Tickets moved back- outdated OS and App Versions - summary
[AE45] = wf.CountIf(.Range("I:I"), "Outdated App Version") + wf.CountIf(.Range("I:I"), "Outdated OS")
End With

Answer 1

这是一个基本示例，应该可以帮助您入门。

Sub 显示如何调用代码：

Sub Tester()
    With ThisWorkbook.Sheets("Main")
        .Range("A1") = GetCount("TT", False, "A:A", "Blue")
        .Range("A2") = GetCount("TT", False, "A:A", "Blue", "C:C", "Red")
        .Range("A3") = GetCount("TT", True, "A:A", "Blue", "C:C", "Red")
    End With

End Sub

用例的通用版本：

'If addValues is True and there are >1 set of criteria then 
'   sum up a bunch of COUNTIF(), else use COUNTIFS() so all 
'   criteria are applied at the same time
Function GetCount(shtName As String, addValues As Boolean, _
                                           ParamArray crit()) As Long
    Dim sht As Worksheet, f As String, num As Long, i As Long
    Set sht = ThisWorkbook.Sheets(shtName)'<< counting things on this sheet
    num = UBound(crit)
    If num = 1 Or addValues Then
        f = "COUNTIF(" & crit(0) & ",""" & crit(1) & """)"
    End If
    If num > 1 Then
        If addValues Then
            'already got the first pair: add the rest
            For i = 2 To num Step 2
                f = f & " + COUNTIF(" & crit(i) & ",""" & crit(i + 1) & """)"
            Next i
        Else
            f = "COUNTIFS("
            For i = 0 To num Step 2
                f = f & crit(i) & ",""" & crit(i + 1) & """"
                If i <> num - 1 Then f = f & ","
            Next i
            f = f & ")"
        End If
    End If
    If f <> "" Then
        Debug.Print f
        GetCount = sht.Evaluate(f) '<<do not use Application.Evaluate here
    Else
        GetCount = -1 '<< something went wrong...
    End If
End Function

调试输出：

COUNTIF(A:A,"Blue")
COUNTIFS(A:A,"Blue",C:C,"Red")
COUNTIF(A:A,"Blue") + COUNTIF(C:C,"Red")

可能可以使用一些错误处理，如果有其他用例，则需要添加这些用例。