首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >查找字符串是否位于二维VBA Excel数组中

查找字符串是否位于二维VBA Excel数组中
EN

Stack Overflow用户
提问于 2015-06-04 20:05:02
回答 6查看 10.5K关注 0票数 3

我有一个很好的函数,我一直对一个一维Excel数组使用它来检查字符串是否在数组中:

代码语言:javascript
运行
复制
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

    IsInArray = (UBound(Filter(arr(), stringToBeFound)) > -1)

End Function

不幸的是,在使用它检查二维数组时,它不能工作,就像我在这里所做的那样:

代码语言:javascript
运行
复制
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

它在函数中不断出错,表示下标超出了范围,有人想我如何检查字符串是否在二维数组中?

EN

Stack Overflow用户

发布于 2017-10-08 16:46:41

@Siddharth上面的答案除了Application.Match函数:-)外,还与Filter完美地工作在一起。-我的解决方案只尝试使用OP Filter函数:由于过滤器函数需要一个1昏暗的数组,所以数组被分割成部分。

A)使用原始筛选函数而不是匹配加上错误处理的替代解决方案

代码语言:javascript
运行
复制
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

帮助函数获取数组维数

代码语言:javascript
运行
复制
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)递归解决方案使用原始的过滤函数而不是匹配加上错误处理

代码语言:javascript
运行
复制
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
票数 0
EN
查看全部 6 条回答
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/30653135

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档