Finding values in an array without looping

If you need to find a value in an array, you might be inclined to loop through the array and using a function like Instr to match each value in the array against the selected value to see if it exists.

Until recently, I would have done the same thing (breaking it out into its own function, of course). But recently I discovered another way to do it that doesn't require looping, and thought I'd share.

Browsing the Microsoft newsgroups led me to a solution posted by Rick Rothstein, an Excel MVP, who used a combination of UBound and Filter to reach a one-line solution that fully replaces a loop. Here's the thread: string compare to array

So here's a function that checks if a given value is in an array:

Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
' checks if valueToFind is found in arr, no loop!
  IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
End Function

Let's test it out by looking for a file extension in an array:

Sub CheckArray()

Dim fileExtensions As Variant

  fileExtensions = Array("doc", "xls")

  MsgBox IsInArray(fileExtensions, "xls")

End Sub

or use an intermediate string variable:

Sub CheckArray()

Dim fileExtensions As Variant
Dim fileExts As String

  fileExts = "doc,xls"

  ' put strings into array, comma-delimited
  fileExtensions = Split(fileExts, ",")

  MsgBox IsInArray(fileExtensions, "xls")

End Sub

For a larger example, see my post in the Outlook forum on VBA Express on saving selected attachments.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 3 Comment(s) on Finding values in an array without looping:

  1. What a neat procedure! Thanks for sharing it with us.

  2. Bob Phillips writes:

    I use a built-in Excel function to get it,

    Function IsInArray(arr As Variant, valueToFind As Variant) As Long
    IsInArray = -1
    On Error Resume Next
    IsInArray = Application.Match(valueToFind, arr, 0)
    End Function

    which has the definite advantage of returning its index within the array.

    And if you want to find a paricular dimension, you can use Index as well

    Function IsInArray(arr As Variant, valueToFind As Variant, Optional dimension As Long = 1) As Long
    Dim i As Long
    IsInArray = -1
    On Error Resume Next
    Do: i = i – (LBound(arr, i + 1) * 0 = 0): Loop Until Err.Number
    If i > 1 Then
    IsInArray = Application.Match(valueToFind, Application.Index(arr, 0, dimension), 0)
    Else
    IsInArray = Application.Match(valueToFind, , arr, 0)
    End If
    End Function

This article is closed to any future comments.
Random Data Generator