查找字符串是否位于二维VBA Excel数组中
查找字符串是否位于二维VBA Excel数组中
提问于 2015-06-04 20:05:02
我有一个很好的函数，我一直对一个一维Excel数组使用它来检查字符串是否在数组中：
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)
End Function不幸的是，在使用它检查二维数组时，它不能工作，就像我在这里所做的那样：
Sub new_idea_filter()
home_sheet = ActiveSheet.Name
c = 1
Dim myfilters(1 To 4, 1 To 5000)
myfilters(1, 4) = "Test"
If IsInArray("Test", myfilters()) = True Then
killer = True
End If
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)
End Function它在函数中不断出错，表示下标超出了范围，有人想我如何检查字符串是否在二维数组中？
回答 6
Stack Overflow用户
回答已采纳
发布于 2015-06-04 20:18:39
来自我的代码集合的东西
您可以使用Application.Match。这将同时适用于1D和2D数组:)
看这个
Sub Sample()
Dim myfilters(1 To 4, 1 To 5000)
myfilters(1, 4) = "Test"
If IsInArray("Test", myfilters()) = True Then MsgBox "Found"
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Dim bDimen As Byte, i As Long
On Error Resume Next
If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
On Error GoTo 0
Select Case bDimen
Case 1
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, arr, 0)
On Error GoTo 0
Case 2
For i = 1 To UBound(arr, 2)
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
On Error GoTo 0
If IsInArray = True Then Exit For
Next
End Select
End FunctionStack Overflow用户
发布于 2015-06-04 20:58:10
只要您在Excel中(或有对它的引用)，就可以使用Index函数将数组分割为行或列。
Public Function IsInArray(ByVal vToFind As Variant, vArr As Variant) As Boolean
Dim i As Long
Dim bReturn As Boolean
Dim vLine As Variant
For i = LBound(vArr, 1) To UBound(vArr, 1)
vLine = Application.WorksheetFunction.Index(vArr, i) 'slice off one line
If IsArray(vLine) Then 'if it's an array, use the filter
bReturn = UBound(Filter(vLine, vToFind)) > -1
Else 'if it's not an array, it was 1d so check the value
bReturn = vLine = vToFind
End If
If bReturn Then Exit For 'stop looking if one found
Next i
IsInArray = bReturn
End Function
Public Sub test()
Dim arr() As Variant
ReDim arr(1 To 2, 1 To 2)
arr(1, 2) = "Test"
Debug.Assert IsInArray("Test", arr)
arr(1, 2) = "Wrong"
Debug.Assert Not IsInArray("Test", arr)
ReDim arr(1 To 3)
arr(2) = "Test"
Debug.Assert IsInArray("Test", arr)
arr(2) = "Wrong"
Debug.Assert Not IsInArray("Test", arr)
Debug.Print "Passed"
End SubStack Overflow用户
发布于 2016-06-26 11:09:45
如果从记录集中获取数据，则使用此方法；首先，我对记录集使用GetString；第二，使用Split在数组一维中转换字符串，其中每个项都是包含所有信息的字符串。之后，您可以使用函数IsInArray。
守则是：
RecSet.Open strSQL, Cn
RecSet.MoveFirst
RecString = RecSet.GetString(, , ";", vbCr) 'genera una cadena con los datos. Campos separados por ; y registros por vbCR
RecSplit = Split(RecString, vbCr) 'Genera un array unidimensional con la cadena您可以测试代码，但记住只有在从记录集中获取数据时才有效。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30653135
复制相关文章
相似问题
腾讯云开发者
