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.