如何在数组中搜索字符串

问题描述

是否有一个简单的(单行)在 VBA 中搜索数组中的字符串?还是我需要遍历每个元素并将其与目标字符串进行比较?

Is there an easy (one-liner) to search for a string within an array in VBA? Or will I need to loop through each element and compare it with the target string?

它是一个一维数组.我只需要知道IF一个字符串在数组中的某处.

It is a one-dimensional array. I only need to know IF a string is somewhere in the array.

浏览器:

names(JOHN, BOB, JAMES, PHLLIP)

我如何确定JOHN"是否在数组中，它需要最小化，因为它会重复大约 5000 次，而且我不希望该函数减慢整个过程.

How do I find out if "JOHN" is in the array, it needs to be minimal as it will be repeated around 5000 times and I don't want the function to slow the overall process down.

推荐答案

如果你想知道字符串是否在数组中找到，试试这个函数:

If you want to know if the string is found in the array at all, try this function:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

正如 SeanC 指出的，这必须是一维数组.

As SeanC points out, this must be a 1-D array.

示例:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print IsInArray("ghi", arr)
End Sub

(以下代码根据来自 HansUp)

(Below code updated based on comment from HansUp)

如果你想要数组中匹配元素的索引，试试这个:

If you want the index of the matching element in the array, try this:

Function IsInArray(stringToBeFound As String, arr As Variant) As Long
  Dim i As Long
  ' default return value if value not found in array
  IsInArray = -1

  For i = LBound(arr) To UBound(arr)
    If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then
      IsInArray = i
      Exit For
    End If
  Next i
End Function

这也假设是一维数组.请记住，LBound 和 UBound 是从零开始的，因此索引 2 表示第三个元素，而不是第二个.

This also assumes a 1-D array. Keep in mind LBound and UBound are zero-based so an index of 2 means the third element, not the second.

示例:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print (IsInArray("ghi", arr) > -1)
End Sub

如果您有一个具体的例子，请用它更新您的问题，否则示例代码可能不适用于您的情况.

这篇关于如何在数组中搜索字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！