Return the position of an element in an array with this function from the VBA Code Library. This UDF is a great tool to have when working with arrays containing unknown elements.
A lot of people recommend using the worksheet function Index to return the position in a VBA array, but I’ve found the performance of that function to be rather slow. Although it loops through each element in your array, I prefer this function.
VBA Find Index in Array
Private Function WhereInArray(arr1 As Variant, vFind As Variant) As Variant
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Function to check where a value is in an array
Dim i As Long
For i = LBound(arr1) To UBound(arr1)
If arr1(i) = vFind Then
WhereInArray = i
Exit Function
End If
Next i
'if you get here, vFind was not in the array. Set to null
WhereInArray = Null
End FunctionMake powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
Looping through the elements in an array until you find a match is an essential skill, but you can do so much more with arrays. To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.
How to use the WhereInArray Function
The WhereInArray UDF accepts two arguments:
- arr1 - the array you want to search
- vFind - the value you want to find in your array.
The function returns the position of the element in your array. If the function can’t find the element in your array, it returns a null string. This is an important to note because if you try to assign this function to an integer or long data type and the element isn’t in your array, you’ll get an error.
Let’s take a look at some examples. We’ll look at a couple good demonstrations, and one bad demonstration.
This example tries to find the string “cat” in an array
Sub Demo_Good()
Dim a(0 To 2) As String
Dim i As Variant
a(0) = "test"
a(1) = "cat"
a(2) = "dog"
i = WhereInArray(a, "cat") 'Will generate a 1
End SubIn this example, we’ll try to find the word “meow” in the array
Sub Demo_Good2()
Dim a(0 To 2) As String
Dim i As Variant
a(0) = "test"
a(1) = "cat"
a(2) = "dog"
i = WhereInArray(a, "meow") 'Returns a value of Null.
End SubThe example above only works because the variable
This what I mean. In this demonstration, we’ll run the same macro except we’ll declare the variable
Sub Demo_Bad()
'Will generate error
Dim a(0 To 2) As String
Dim i As Integer
a(0) = "test"
a(1) = "cat"
a(2) = "dog"
i = WhereInArray(a, "meow") 'Will generate "Invalid use of Null" (Error 94)
End SubLike I promised, you get a run-time error.
Why a Variant?
Why do I prefer to output my index position as a variant instead of a long or integer? I do this so I can use this function in two different ways.
If I don’t really care where the element is in the array but I want to know whether or not the element is in the array, I can check for Null using the IsNull function to see if the value I’m curious about is anywhere in my array. In that regard, it functions like my IsInArray UDF.
Take a look at this example to see what I mean:
Sub Demo_IsNullCheck()
Dim a(0 To 2) As String
a(0) = "test"
a(1) = "cat"
a(2) = "dog"
If IsNull(WhereInArray(a, "meow")) Then
'Value is not in array
MsgBox "Element Not Found!"
Else
'value is in array
MsgBox "Element Was Found!"
End If
End SubFor more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below. Once you join, share this article on Twitter and Facebook.
