vba - 减少资源计算搜索功能

Question

我目前正在编写一个执行以下操作的函数:在字符串中搜索一系列关键字的匹配项。但是我的代码效率太低，运行时间太长。有什么方法可以简化它并减少资源密集型？

Function SearchV(text As String, wordlist As Range)
Dim res As Variant
Dim match As Long

On Error Resume Next

For Each res In wordlist
match = InStr(UCase(text), UCase(res))

If match > 0 Then
SearchV = UCase(res)
Exit Function
End If

Next res

If match = 0 Then
SearchV = ""
End If
End Function

Answer 1

更新:

我正在提供另一种拆分 text 的方法根据单词之间的空格放入一个数组中。我知道你说可能有一些句点或额外的空格(在下面的评论中)，但我已经将它们考虑在内，你可以轻松地添加它以删除更多不稳定的字符。

在文本方法中搜索每个单词

Function SearchV(text As String, wordlist As Range)

Dim arr() As String, x As Long, rYes As Range

arr() = Split(text, " ") 'split text into array of words based on space

For i = LBound(arr) To UBound(arr)

    Dim sTest As String
    sTest = Replace(arr(i), ".", "") 'remove any periods
    sTest = Replace(sTest, " ", "") 'remove additional space

    Set rYes = wordlist.Find(sTest)

    If Not rYes Is Nothing Then

        SearchV = rYes
        Exit Function

    End If

Next

SearchV = ""

End Function

搜索数组方法

根据您对我(和其他人的回答)的评论，我将其修改为循环遍历数组，而不是范围。这可能会或可能不会有助于提高速度，但我想展示它，因为无论如何我都发布了答案，它可能值得测试。我还提前将一些变量设置为 UCase 字符串，这可能很有用。

Function SearchV(text As String, wordlist As Range)

Dim arr() As Variant
Dim sTest As String, sAgainst As String

sAgainst = UCase$(text) 'set

arr() = wordlist

For x = LBound(arr) To UBound(arr)

    sTest = UCase$(arr(x, 1))

    If InStr(1, sAgainst, sTest) Then

        SearchV = sTest
        Exit Function

    End If

Next

SearchV = ""

End Function