Return Position of Element in VBA Array

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 spreadsheet 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 a. “Cat” is stored in the 1st index position (0, 1, 2), so the function returns a value of 1.

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 a. Notice that “meow” isn’t in the array. When we search for it, the WhereInArray function returns a value of Null.

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 i was declared as a Variant. If it were defined as a Long or an Integer, it would generate Run-time Error 94, “Invalid use of Null.”

This what I mean. In this demonstration, we’ll run the same macro except we’ll declare the variable i as an integer.

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

When you’re ready to start writing your macros faster (without having to search online for answers) check out our best-selling VBA Cheat Sheets for some powerful automation tricks. For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.

Share this article on Twitter and Facebook, then leave a comment below and let’s have a discussion.

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 spreadsheet with just one click.