VBA Excel Use a value as any part of an array

Question

A really basic question I m afraid:

How do I use a value as any part of an array?

Sub test()

Dim x As Variant
x = Array(1, 2, 3, 8, 9, 10, 1585)

    If InStr(Cells(1,1).Value, x) Then
        MsgBox "OK"
    End If

End Sub

In this code I am trying to check if the cell contains any value from the array

I don't want to complicate the question too much, but it's important to consider some other factors, namely the array size and the number of searches against the array per "session." If you are actually working with a 5 - 10 element array, then it won't make much of a difference. But, if you are loading a large array into memory, and performing a number of searches against it, you can expect performance issues. — basodre
– basodre, Commented Aug 17, 2018 at 13:09

SJR · Accepted Answer · 2018-08-17 10:20:17Z

1

You can use Match and check whether a number is returned (which means the value is found in the array).

Sub test()

Dim x As Variant

x = Array(1, 2, 3, 8, 9, 10, 1585)

If IsNumeric(Application.Match(Cells(1, 1).Value, x, 0)) Then
    MsgBox "OK"
End If

End Sub

answered Aug 17, 2018 at 10:20

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shoeless · Accepted Answer · 2018-08-17 11:10:39Z

1

Alternatively, if it doesn't have to be an array one could simply use a string as such:

Sub test()

Dim arrayString As String

  arrayString = "1, 2, 3, 8, 9, 10, 1585"
  If InStr(1, arrayString, Cells(1, 1), vbTextCompare) > 0 Then
    MsgBox "OK"
  End If

End Sub

answered Aug 17, 2018 at 11:10

Shoeless

193 bronze badges

Collectives™ on Stack Overflow

VBA Excel Use a value as any part of an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related