我有一个很好的函数,我一直对一个一维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
它在函数中不断出错,表示下标超出了范围,有人想我如何检查字符串是否在二维数组中?
发布于 2017-10-08 08:46:41
@Siddharth上面的答案除了Application.Match
函数:-)外,还与Filter
完美地工作在一起。-我的解决方案只尝试使用OP Filter
函数:由于过滤器函数需要一个1昏暗的数组,所以数组被分割成部分。
A)使用原始筛选函数而不是匹配加上错误处理的替代解决方案
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Dim i As Long
If nDim(arr) = 1 Then
IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)
Else ' allows using filter function in portions
For i = 1 To UBound(arr, 2)
If (UBound(Filter(Application.Transpose(Application.Index(arr, 0, i)), stringToBeFound)) > -1) Then IsInArray = True: Exit For
Next i
End If
End Function
帮助函数获取数组维数
Function nDim(ByVal vArray As Variant) As Long
' Purp: get number of array dimensions
' Site: http://support.microsoft.com/kb/152288
Dim dimnum As Long
Dim ErrorCheck As Variant
On Error GoTo FinalDimension
For dimnum = 1 To 60000
ErrorCheck = LBound(vArray, dimnum)
Next
FinalDimension:
nDim = dimnum - 1
End Function
B)递归解决方案使用原始的过滤函数而不是匹配加上错误处理
Function IsInArray(stringToBeFound As String, arr As Variant, Optional i As Long = 0) As Boolean
Select Case i
Case -1: ' stop 2dim calls
Case 0: IsInArray = IsInArray(stringToBeFound, arr, nDim(arr)) ' start recursive call
Case 1: IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1) ' 1dim array
Case Else ' allows using filter function in portions
If (UBound(Filter(Application.Transpose(Application.Index(arr, 0, i)), stringToBeFound)) > -1) Then
IsInArray = True
Else ' recursive calls (2dim array)
IsInArray = IsInArray(stringToBeFound, arr, IIf(i + 1 > UBound(arr), -1, i + 1))
End If
End Select
End Function
https://stackoverflow.com/questions/30653135
复制