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 Function
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.
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
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 Sub
In 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 Sub
The 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 Sub
Like 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 Sub
Well, that’s all for today. Reach out to my VBA Consulting page if you’re working on a macro, but you can’t quite figure out how to finish it. I’ll be happy to help you out!
If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products. There are plenty more grab-and-go macro examples in my VBA Code Library so take a look there, too!