如何计算一个字符串在 VBA 中出现在另一个字符串中的次数？

How do I count the number of times a string appears within another string in VBA?

提问by Sinister Beard

How do I count the number of times one string appears within another in Access VBA? For example, how would I count how many times "the" occurs in "The quick brown fox jumps over the lazy dog."?

如何计算一个字符串在 Access VBA 中出现在另一个字符串中的次数？例如，如何计算“The quick brown fox jumps over the lazy dog.”中“the”出现的次数？

回答by Alex K.

As you are ok with substrings/case sensitivity

因为你对子字符串/区分大小写没问题

matches = (len(lookin) - len(replace$(lookin, find, ""))) / len(find)

回答by PaulFrancis

You can simplify the function with far less variables and avoid the usage of For by the following function.

您可以使用更少的变量简化函数，并避免以下函数使用 For 。

Public Function getOccuranceCount(Expression As String, Find As String) As Long
'*******************************************************************************
'Code Courtesy of
'  Paul Eugin
'
'   Input  - Expression, the String to check
'            Find, the String pattern to be checked for
'   Output - The number of occurance of the Find String in the Expression String
'   Usage  - getOccuranceCount("The quick brown fox jumped over the lazy dog.","saw")
'               0
'            getOccuranceCount("The quick brown fox jumped over the lazy dog.","the")
'               2
'*******************************************************************************
On Error GoTo errDisplay
    Dim strArr() As String, i As Long
    strArr = Split(Expression, Find)
    getOccuranceCount = UBound(strArr)

errExit:
    Exit Function
errDisplay:
    MsgBox "The following error has occured while trying to get the count." & vbCrLf & vbCrLf & _
           "Error (" & Err.Number & ") - " & Err.Description, vbCritical, "Contact the DB Admin."
Resume errExit
End Function

The function will Split it into an array then all you need is the Count of the Boundaries. Hope this helps.

该函数会将其拆分为一个数组，然后您只需要边界计数。希望这可以帮助。

回答by Brad

Dim lookin As String
Dim tofind As String
lookin = "The quick brown fox jumps over the lazy dog."
tofind = "the "

Dim r As Object, matches
Set r = CreateObject("VBScript.RegExp")

r.Pattern = tofind
r.IgnoreCase = True
r.Multiline = False
r.Global = True
Set matches = r.Execute(lookin)

matches finds two hits. One at index = 0 and one at index = 31.

匹配找到两个命中。一个在索引 = 0 和一个在索引 = 31。

回答by Sinister Beard

You can use this function, which uses InStr:

您可以使用这个函数，它使用 InStr：

Function CountStringOccurances(strStringToCheck As String, strValueToCheck As String) As Integer
'Purpose: Counts the number of times a string appears in another string.
On Error GoTo ErrorMessage
    Dim intStringPosition As Integer
    Dim intCursorPosition As Integer
    CountStringOccurances = 0
    intCursorPosition = 1
    For i = 0 To Len(strStringToCheck)
        intStringPosition = InStr(intCursorPosition, strStringToCheck, strValueToCheck)
        If intStringPosition = 0 Then
            Exit Function
        Else
            CountStringOccurances = CountStringOccurances + 1
            intCursorPosition = intStringPosition + Len(strValueToCheck)
        End If
    Next i
    Exit Function
ErrorMessage:
MsgBox "The database has generated an error. Please contact the database administrator, quoting the following error message: '" & Err.Description & "'", vbCritical, "Database Error"
End
End Function

The function returns the position of your value within the string. If that's 0, it exits and returns the number. If it returns a positive value (and it will if the value is present), then it adds one to the number of occurances, and then checks again, moving its starting position to just after the previous occurance. This function is case insenstive.

该函数返回您的值在字符串中的位置。如果为 0，则退出并返回数字。如果它返回一个正值（如果该值存在，它会返回），那么它会在出现次数上加一，然后再次检查，将其起始位置移动到前一次出现之后。此功能不区分大小写。

Using the examples above:

使用上面的例子：

CountStringOccurances("The quick brown fox jumped over the lazy dog.","the")

would return 2.

将返回 2。

回答by Tim Edwards

You can use Split in the same solution as this questionhas. It's the tidiest solution that I've seen so far.

您可以在与此问题相同的解决方案中使用 Split 。这是迄今为止我见过的最整洁的解决方案。