使用 Excel VBA 检查值是否在数组中

Question

提问by Ryflex

I've got some code below, that is supposed to be checking if a value is in an Array or not.

我在下面有一些代码，应该是检查值是否在数组中。

Sub test()
    vars1 = Array("Examples")
    vars2 = Array("Example")
    If IsInArray(Range("A1").Value, vars1) Then
        x = 1
    End If

    If IsInArray(Range("A1").Value, vars2) Then
        x = 1
    End If
End Sub

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

If the cell A1contains the word Examplesfor some reason both of the IsInArraydetects it as existing for both Arrays when it should only find it existing in the vars1array

如果单元格A1中包含单词Examples出于某种原因两个的IsInArray检测为当它应该只找到现有的两个阵列它现有的在vars1阵

What do I need to change to make my IsInArrayfunction to make it an exact match?

我需要更改什么才能使我的IsInArray函数完全匹配？

Answer 1

回答by Brad

You can brute force it like this:

你可以像这样暴力破解：

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

Use like

使用喜欢

IsInArray("example", Array("example", "someother text", "more things", "and another"))

Answer 2

回答by Ryflex

This Question was asked here: VBA Arrays - Check strict (not approximative) match

这个问题在这里被问到：VBA Arrays - Check strict (not approximative) match

Sub test()
    vars1 = Array("Examples")
    vars2 = Array("Example")
    If IsInArray(Range("A1").value, vars1) Then
        x = 1
    End If

    If IsInArray(Range("A1").value, vars2) Then
        x = 1
    End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

Answer 3

回答by Sixthsense

Use Match() function in excel VBA to check whether the value exists in an array.

使用 Excel VBA 中的 Match() 函数检查数组中是否存在该值。

Sub test()
    Dim x As Long

    vars1 = Array("Abc", "Xyz", "Examples")
    vars2 = Array("Def", "IJK", "MNO")

    If IsNumeric(Application.Match(Range("A1").Value, vars1, 0)) Then
        x = 1
    ElseIf IsNumeric(Application.Match(Range("A1").Value, vars2, 0)) Then
        x = 1
    End If

    MsgBox x
End Sub

Answer 4

回答by Sammy Douglas

I searched for this very question and when I saw the answers I ended up creating something different (because I favor less code over most other things most of the time) that should work in the vast majority of cases. Basically turn the array into a string with array elements separated by some delimiter character, and then wrap the search value in the delimiter character and pass through instr.

我搜索了这个问题，当我看到答案时，我最终创建了一些不同的东西（因为在大多数情况下，我喜欢用更少的代码而不是大多数其他东西），这些东西应该在绝大多数情况下都有效。基本上把数组变成一个字符串，数组元素由一些定界符分隔，然后将搜索值包裹在定界符中并通过instr。

Function is_in_array(value As String, test_array) As Boolean
    If Not (IsArray(test_array)) Then Exit Function
    If InStr(1, "'" & Join(test_array, "'") & "'", "'" & value & "'") > 0 _
        Then is_in_array = True
End Function

And you'd execute the function like this:

你会像这样执行函数：

test = is_in_array(1, array(1, 2, 3))

Answer 5

回答by Marcucciboy2

While this is essentially just @Brad's answer again, I thought it might be worth including a slightly modified function which will return the index of the item you're searching for if it exists in the array. If the item is not in the array, it returns -1instead.

虽然这基本上只是@Brad 的回答，但我认为可能值得包含一个稍微修改的函数，该函数将返回您正在搜索的项目的索引（如果它存在于数组中）。如果该项目不在数组中，则返回-1。

The output of this can be checked just like the "in string" function, If InStr(...) > 0 Then, so I made a little test function below it as an example.

这个的输出可以像“in string”函数一样检查If InStr(...) > 0 Then，所以我在它下面做了一个小测试函数作为例子。

Option Explicit

Public Function IsInArrayIndex(stringToFind As String, arr As Variant) As Long

    IsInArrayIndex = -1

    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i) = stringToFind Then
            IsInArrayIndex = i
            Exit Function
        End If
    Next i

End Function

Sub test()

    Dim fruitArray As Variant
    fruitArray = Array("orange", "apple", "banana", "berry")

    Dim result As Long
    result = IsInArrayIndex("apple", fruitArray)

    If result >= 0 Then
        Debug.Print chr(34) & fruitArray(result) & chr(34) & " exists in array at index " & result
    Else
        Debug.Print "does not exist in array"
    End If

End Sub

Then I went a little overboard and fleshed out one for two dimensional arrays because when you generate an array based on a rangeit's generally in this form.

然后我有点过火，并为二维数组充实了一个，因为当您基于范围生成数组时，它通常采用这种形式。

It returns a single dimension variant array with just two values, the two indices of the array used as an input (assuming the value is found). If the value is not found, it returns an array of (-1, -1).

它返回一个只有两个值的单维变量数组，数组的两个索引用作输入（假设找到了该值）。如果未找到该值，则返回一个(-1, -1).

Option Explicit

Public Function IsInArray2DIndex(stringToFind As String, arr As Variant) As Variant

    IsInArray2DIndex= Array(-1, -1)

    Dim i As Long
    Dim j As Long

    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If arr(i, j) = stringToFind Then
                IsInArray2DIndex= Array(i, j)
                Exit Function
            End If
        Next j
    Next i

End Function

Here's a picture of the data that I set up for the test, followed by the test:

这是我为测试设置的数据的图片，然后是测试：

test 2

测试 2

Sub test2()

    Dim fruitArray2D As Variant
    fruitArray2D = sheets("Sheet1").Range("A1:B2").value

    Dim result As Variant
    result = IsInArray2DIndex("apple", fruitArray2D)

    If result(0) >= 0 And result(1) >= 0 Then
        Debug.Print chr(34) & fruitArray2D(result(0), result(1)) & chr(34) & " exists in array at row: " & result(0) & ", col: " & result(1)
    Else
        Debug.Print "does not exist in array"
    End If

End Sub

Answer 6

回答by Sreekumar Menon K

The below function would return '0' if there is no match and a 'positive integer' in case of matching:

如果没有匹配，下面的函数将返回“0”，如果匹配则返回“正整数”：

Function IsInArray(stringToBeFound As String, arr As Variant) As Integer IsInArray = InStr(Join(arr, ""), stringToBeFound) End Function______________________________________________________________________________

Function IsInArray(stringToBeFound As String, arr As Variant) As Integer IsInArray = InStr(Join(arr, ""), stringToBeFound) End Function________________________________________________________________________________________

Note: the function first concatenates the entire array content to a string using 'Join' (not sure if the join method uses looping internally or not) and then checks for a macth within this string using InStr.

注意：该函数首先使用 'Join' 将整个数组内容连接到一个字符串（不确定 join 方法是否在内部使用循环），然后使用 InStr 检查该字符串中的 macth。

Answer 7

回答by Andreas Dietrich

I would like to provide another variant that should be both performant and powerful, because

我想提供另一个既高效又强大的变体，因为

it does not use the sometimes slower Match)
supports String, Integer, Booleanetc.(not String-only)
returns the indexof the searched-for item
supports nth-occurrence

它不使用有时较慢的Match）
支撑件String，Integer，Boolean等等（未String-only）
返回搜索项的索引
支持第 n 次出现

...

'-1 if not found
'https://stackoverflow.com/a/56327647/1915920
Public Function IsInArray( _
  item As Variant, _
  arr As Variant, _
  Optional nthOccurrence As Long = 1 _
  ) As Long

    IsInArray = -1

    Dim i As Long:  For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i) = item Then
            If nthOccurrence > 1 Then
                nthOccurrence = nthOccurrence - 1
                GoTo continue
            End If
            IsInArray = i
            Exit Function
        End If
continue:
    Next i

End Function

use it like this:

像这样使用它：

Sub testInt()
  Debug.Print IsInArray(2, Array(1, 2, 3))  '=> 1
End Sub

Sub testString1()
  Debug.Print IsInArray("b", Array("a", "b", "c", "a"))  '=> 1
End Sub

Sub testString2()
  Debug.Print IsInArray("b", Array("a", "b", "c", "b"), 2)  '=> 3
End Sub

Sub testBool1()
  Debug.Print IsInArray(False, Array(True, False, True))  '=> 1
End Sub

Sub testBool2()
  Debug.Print IsInArray(True, Array(True, False, True), 2)  '=> 2
End Sub

Answer 8

回答by Sumantha Poojary

You want to check whether Examplesexists in Range("A1").Value If it fails then to check Exampleright? I think mycode will work perfect. Please check.

要检查是否例如存在于范围（“A1”）。值，如果它失败，那就要检查一下实例吧？我认为 mycode 会很完美。请检查。

Sub test()
Dim string1 As String, string2 As String
string1 = "Examples"
string2 = "Example"
If InStr(1, Range("A1").Value, string1) > 0 Then
    x = 1
ElseIf InStr(1, Range("A1").Value, string2) > 0 Then
    x = 2
End If

End Sub

结束子

使用 Excel VBA 检查值是否在数组中

提问by Ryflex

回答by Brad

回答by Ryflex

回答by Sixthsense

回答by Sammy Douglas

回答by Marcucciboy2

回答by Sreekumar Menon K

回答by Andreas Dietrich

回答by Sumantha Poojary

相关推荐

最近更新

标签

使用 Excel VBA 检查值是否在数组中

提问by Ryflex

回答by Brad

回答by Ryflex

回答by Sixthsense

回答by Sammy Douglas

回答by Marcucciboy2

回答by Sreekumar Menon K

回答by Andreas Dietrich

回答by Sumantha Poojary

相关推荐

在 VBA Access 中使用 .text 和 .value 的区别

vba 如何另存为xlsx文件？

卡住。我需要一些 VBA 代码来在 excel 范围内的每个单元格中的字符串周围插入双引号

按名称查找列标题并选择列标题下方的所有数据 (Excel-VBA)

相关推荐

最近更新

标签