VBA finding if value is in values

Question

I have a value y, and I want to find out if that value is inside this set of values : x1, x2, ... xn.

I can do it like this:

if(y = x1 or y = x2 or .....)

But is there a nicer way? pseudocode:

if(y in (x1, x2, ...., xn))

Answer 1

You could write a helper function like this one:

Public Function FindValue(ByVal ValueToFind As Variant, ParamArray SearchIn() As Variant)

    Dim i As Integer

    For i = 0 To UBound(SearchIn)
        If SearchIn(i) = ValueToFind Then
            FindValue = True
            Exit Function
        End If
    Next

End Function

The second parameter (the ParamArray) is an array, so you can actually pass an indefinite number of parameters.

So you can just pass all your values to this function - the one you want to find first, and after that all the ones you want to search in:

Dim Found As Boolean

Found = FindValue(y, x1, x2, x3, xn)

Answer 2

You can use Select Case in the same manner of (if then else):

Select Case Y
       Case X1, X2, X3, ...
            Do if True
       Case Else
            Do if False
End Select

Answer 3

Use arrays:

dim x(10)

x(1)=....
x(2)=....

y=....

for i=1 to 10
    if x(i)=y then
         ....
    end if
next i