vba 匹配字符串数组中的值

Matching values in string array

提问by David Zemens

Problem: Looking for a more efficient way of finding whether there is an exact matching value in a 1d array -- essentially a boolean true/false.

问题：寻找一种更有效的方法来查找一维数组中是否存在精确匹配的值——本质上是一个 boolean true/false。

Am I overlooking something obvious?Or am I simply using the wrong data structure, by using an array when I probably should be using a collection object or a dictionary? In the latter I could check the .Containsor .Existsmethod, respectively

我是否忽略了一些明显的东西？或者我只是使用错误的数据结构，在我可能应该使用集合对象或字典时使用数组？在后者中，我可以分别检查.Containsor.Exists方法

In Excel I can check for a value in a vector array like:

在 Excel 中，我可以检查向量数组中的值，例如：

If Not IsError(Application.Match(strSearch, varToSearch, False)) Then
' Do stuff
End If

This returns an exact match index, obviously subject to limitations of Matchfunction which only finds the firstmatching value in this context. This is a commonly used method, and one that I have been using for a long time, too.

这将返回一个精确匹配索引，显然受到Match函数的限制，它只能在此上下文中找到第一个匹配值。这是一种常用的方法，也是我长期使用的方法。

This is satisfactory enough for Excel -- but what about other applications?

这对于 Excel 来说已经足够令人满意了——但是对于其他应用程序呢？

In other applications, I can do basically the same thing but requires enabling reference to the Excel object library, and then:

在其他应用程序中，我可以做基本相同的事情，但需要启用对 Excel 对象库的引用，然后：

   If Not IsError(Excel.Application.match(...))

That seems silly, though, and is difficult to manage on distributed files because of permissions/trust center/etc.

但是，这似乎很愚蠢，并且由于权限/信任中心/等原因，很难管理分布式文件。

I have tried to use the Filter()function:

我尝试使用Filter()函数：

 If Not Ubound(Filter(varToSearch, strSearch)) = -1 Then
    'do stuff
 End If

But the problem with this approach is that Filterreturns an array of partial matches, rather than an array of exact matches. (I have no idea why it would be useful to return substring/partial matches.)

但是这种方法的问题是Filter返回部分匹配的数组，而不是精确匹配的数组。（我不知道为什么返回子字符串/部分匹配会很有用。）

The other alternative is to literally iterate over each value in the array (this also is very commonly used I think) -- which seems even more needlessly cumbersome than calling on Excel's Matchfunction.

另一种选择是逐字迭代数组中的每个值（我认为这也是非常常用的）——这似乎比调用 Excel 的Match函数更麻烦。

For each v in vArray
   If v = strSearch Then
    ' do stuff
   End If
Next

回答by Tim Williams

If we're going to talk about performance then there's no substutute for running some tests. In my experience Application.Match() is up to ten times slower than calling a function which uses a loop.

如果我们要谈论性能，那么运行一些测试就没有替代品了。根据我的经验 Application.Match() 比调用使用循环的函数慢十倍。

Sub Tester()

    Dim i As Long, b, t
    Dim arr(1 To 100) As String

    For i = 1 To 100
        arr(i) = "Value_" & i
    Next i

    t = Timer
    For i = 1 To 100000
        b = Contains(arr, "Value_50")
    Next i
    Debug.Print "Contains", Timer - t

    t = Timer
    For i = 1 To 100000
        b = Application.Match(arr, "Value_50", False)
    Next i
    Debug.Print "Match", Timer - t

End Sub


Function Contains(arr, v) As Boolean
Dim rv As Boolean, lb As Long, ub As Long, i As Long
    lb = LBound(arr)
    ub = UBound(arr)
    For i = lb To ub
        If arr(i) = v Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

Output:

输出：

Contains       0.8710938 
Match          4.210938 

回答by GrzMat

I used to look for a best replace solution. It should work for simple finding as well.

我曾经寻找最好的替代解决方案。它也应该适用于简单的查找。

To find first instance of a string you can try using this code:

要查找字符串的第一个实例，您可以尝试使用以下代码：

Sub find_strings_1()

Dim ArrayCh() As Variant
Dim rng As Range
Dim i As Integer

 ArrayCh = Array("a", "b", "c")

With ActiveSheet.Cells
    For i = LBound(ArrayCh) To UBound(ArrayCh)
        Set rng = .Find(What:=ArrayCh(i), _
        LookAt:=xlPart, _
        SearchOrder:=xlByColumns, _
        MatchCase:=False)

        Debug.Print rng.Address

    Next i
End With

End Sub

If you want to find all instances try the below.

如果您想查找所有实例，请尝试以下操作。

Sub find_strings_2()

Dim ArrayCh() As Variant
Dim c As Range
Dim firstAddress As String
Dim i As Integer

 ArrayCh = Array("a", "b", "c") 'strings to lookup

With ActiveSheet.Cells
    For i = LBound(ArrayCh) To UBound(ArrayCh)
        Set c = .Find(What:=ArrayCh(i), LookAt:=xlPart, LookIn:=xlValues)

        If Not c Is Nothing Then
            firstAddress = c.Address 'used later to verify if looping over the same address
            Do
                '_____
                'your code, where you do something with "c"
                'which is a range variable,
                'so you can for example get it's address:
                Debug.Print ArrayCh(i) & " " & c.Address 'example
                '_____
                Set c = .FindNext(c)

            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    Next i
End With

End Sub

Keep in mind that if there are several instances of searched string within one cell it will return only one result due to the specific of FindNext.

请记住，如果在一个单元格中有多个搜索字符串的实例，由于 FindNext 的特殊性，它只会返回一个结果。

Still, if you need a code for replacing found values with another, I'd use the first solution, but you'd have to change it a bit.

尽管如此，如果您需要一个代码来用另一个替换找到的值，我会使用第一个解决方案，但您必须对其进行一些更改。

回答by Ioannis

"A more efficient way (compared to Application.Match)of finding whether a string value exists in an array":

“Application.Match查找数组中是否存在字符串值的更有效方法（与 相比）”：

I believe there is no more efficient way than the one you are using, i.e., Application.Match.

我相信没有比您正在使用的方法更有效的方法了，即Application.Match.

Arrays allow efficient access in any element if we know the index of that element. If we want to do anything by element value (even checking if an element exists), we have to scan all the elements of the array in the worst case. Therefore, the worst case needs nelement comparisons, where nis the size of the array. So the maximum time we need to find if an element exists is linear in the size of the input, i.e., O(n). This applies to any language that uses conventional arrays.

如果我们知道该元素的索引，则数组允许对任何元素进行有效访问。如果我们想通过元素值做任何事情（甚至检查元素是否存在），我们必须在最坏的情况下扫描数组的所有元素。因此，最坏的情况需要n元素比较，其中n是数组的大小。因此，我们需要查找元素是否存在的最长时间与输入的大小成线性关系，即O(n)。这适用于任何使用传统数组的语言。

The only case where we can be more efficient, is when the array has special structure. For your example, if the elements of the array are sorted (e.g. alphabetically), then we do not need to scan all the array: we compare with the middle element, and then compare with the left or right part of the array (binary search). But without assuming any special structure, there is no hope..

唯一可以提高效率的情况是数组具有特殊结构。对于您的示例，如果数组的元素已排序（例如按字母顺序），那么我们不需要扫描所有数组：我们与中间元素进行比较，然后与数组的左侧或右侧部分进行比较（二分查找)。但是不假设任何特殊的结构，就没有希望。

The Dictionary/Collectionas you point, offers constant key access to their elements (O(1)). What perhaps is not very well documented is that one can also have index accessto the dictionary elements (Keys and Items): the order in which elements are entered into the Dictionaryis preserved. Their main disadvantage is that they use more memory as two objects are stored for each element.

在Dictionary/Collection你点，不断提供密钥访问他们的内容（O(1)）。可能没有很好记录的是，人们还可以对字典元素（键和项目）进行索引访问：Dictionary保留元素输入的顺序。它们的主要缺点是它们使用更多内存，因为每个元素存储两个对象。

To wrap up, although If Not IsError(Excel.Application.match(...))looks silly, it is still the more efficient way (at least in theory). On permission issues my knowledge is very limited. Depending on the host application, there are always some Find-type functions (C++has findand find_iffor example).

总结一下，虽然If Not IsError(Excel.Application.match(...))看起来很傻，但它仍然是更有效的方式（至少在理论上）。关于许可问题，我的知识非常有限。根据主机应用程序，总是有一些Find-type 函数（C++hasfind和find_if例如）。

I hope that helps!

我希望这有帮助！

Edit

编辑

I would like to add a couple of thoughts, after reading the amended version of the post and Tim's answer. The above text is focusing on the theoretical time complexity of the various data structures and ignores implementation issues. I think the spirit of the question was rather, "given a certain data structure (array)", what is the most efficient way in practiceof checking existence.

在阅读了帖子的修订版和蒂姆的回答后，我想补充几点想法。上面的文字侧重于各种数据结构的理论时间复杂度，而忽略了实现问题。我认为问题的精神是“给定某个数据结构（数组）”，在实践中检查存在的最有效方法是什么。

To this end, Tim's answer is an eye-opener.

为此，Tim 的回答让人大开眼界。

The conventional rule "if VBAcan do it for you then don't write it again yourself" is not always true. Simple operations like looping and comparisons can be faster that "agreegate" VBAfunctions. Two interesting links are hereand here.

传统的规则“如果VBA可以为你做，就不要自己再写”并不总是正确的。像循环和比较这样的简单操作可以比“同意”VBA函数更快。这里和这里有两个有趣的链接。