UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> IsIn Find an Item in a List    
IsIn Find an Item in a List

ISIN – Determine if item is in a list

SQL has the IN function. Excel VBA does not.

Acknowledgement: My thanks to David Marten (AKA cheekybuddha) for enhancing my code.

The syntax is IsIn(Look_For, List_Item1, List_Item2, …)

For example: IsIn (“B”, “A”, “B”, “C”) will look for “B” in the list “A”, “B”, “C.”

The function returns the position on the list where the item is found. In the example above, this is 2. If the item is not found then the function returns zero.

CODE

Option Explicit

Function IsIn(MyValue As Variant, ParamArray MyList() As Variant) As Long
Dim k As Long
Dim Result As Long

Result = 0

For k = 0 To UBound(MyList) - 1
   If MyList(k) = MyValue Then
       Result = k + 1
       Exit For
   End If
Next

IsIn = Result
       
End Function

Sub foo()

MsgBox IsIn("B", "A", "B", "C")

End Sub
Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 1,907 times.  This page was last modified 20:37, 12 November 2012 by dflak.   Disclaimers