Finding values in an array without looping
September 28, 2009 • JP • No Comments • Rate This Article
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:
' 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:
Dim fileExtensions As Variant
fileExtensions = Array("doc", "xls")
MsgBox IsInArray(fileExtensions, "xls")
End Sub
or use an intermediate string variable:
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.
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
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 if you want to learn more about VBA. Keep Reading »
Previous Post: Useful toolbar buttons you don't see every day in Excel 2003, Part 3
Next Post: Working with the del.icio.us API in VBA
What a neat procedure! Thanks for sharing it with us.
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
For some reason, I was never able to get this approach to work. I'll have to revisit it.