vba 如何查找数组是否包含字符串

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/11109832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 13:20:39  来源:igfitidea点击:

How to find if an array contains a string

arraysvbasubstring

提问by Nicola-V

Possible Duplicate:
How to search for string in MS Access VBA array

可能的重复:
如何在 MS Access VBA 数组中搜索字符串

I am currently working on an Excel macro, and I could not find a way to do like if array.contains(mystring)

我目前正在处理 Excel 宏,但找不到类似的方法 if array.contains(mystring)

I wrote the following, and it gives me the message "Invaild Qualifier" and highlights the Mainframright after If

我写了下面,它给我的信息“Invaild资格赛”,并强调了Mainfram之后If

Dim Mainfram(4) As String

Mainfram(0) = "apple"

Mainfram(1) = "pear"

Mainfram(2) = "orange"

Mainfram(3) = "fruit"

    For Each cel In Selection
        If Mainfram.Contains(cel.Text) Then
            Row(cel.Row).Style = "Accent1"
        End If
    Next cel

The selection is a column

选择是一列

Anyone help?

有人帮忙吗?

Hi, JP I tried your suggestion, and it said Object required. And Highlightd the If IsInArray(cell.Text, Mainfram) ThenHeres my full code

嗨,JP 我试过你的建议,它说需要对象。并突出显示 If IsInArray(cell.Text, Mainfram) ThenHeres my full code

Sub changeRowColor()

Columns("B:B").Select

Dim cel As Excel.Range
Dim Mainfram(4) As String

Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "Banana"

For Each cel In Selection
    If IsInArray(cell.Value, Mainfram) Then
        Rows(cel.Row).Style = "Accent1"
    End If
Next cel

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

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

End Function

Nevermind, I found that stupid Error... Thank you anyways

没关系,我发现了那个愚蠢的错误......还是谢谢你

回答by JimmyPena

Using the code from my answerto a very similar question:

使用对一个非常相似问题的回答中的代码:

Sub DoSomething()
Dim Mainfram(4) As String
Dim cell As Excel.Range

Mainfram(0) = "apple"
Mainfram(1) = "pear"
Mainfram(2) = "orange"
Mainfram(3) = "fruit"

For Each cell In Selection
  If IsInArray(cell.Value, MainFram) Then
    Row(cell.Row).Style = "Accent1"
  End If
Next cell

End Sub

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

回答by Siddharth Rout

Another simple way using JOINand INSTR

使用JOIN和的另一种简单方法INSTR

Sub Sample()
    Dim Mainfram(4) As String, strg As String
    Dim cel As Range
    Dim Delim As String

    Delim = "#"

    Mainfram(0) = "apple"
    Mainfram(1) = "pear"
    Mainfram(2) = "orange"
    Mainfram(3) = "fruit"

    strg = Join(Mainfram, Delim)
    strg = Delim & strg

    For Each cel In Selection
        If InStr(1, strg, Delim & cel.Value & Delim, vbTextCompare) Then _
        Rows(cel.Row).Style = "Accent1"
    Next cel
End Sub

回答by Jon Egerton

I'm afraid I don't think there's a shortcut to do this - if only someone would write a linq wrapper for VB6!

恐怕我不认为有捷径可以做到这一点 - 如果有人会为 VB6 编写一个 linq 包装器就好了!

You could write a function that does it by looping through the array and checking each entry - I don't think you'll get cleaner than that.

您可以编写一个函数,通过遍历数组并检查每个条目来执行此操作 - 我认为您不会比这更简洁。

There's an example article that provides some details here: http://www.vb6.us/tutorials/searching-arrays-visual-basic-6

有一篇示例文章在这里提供了一些详细信息:http: //www.vb6.us/tutorials/searching-arrays-visual-basic-6