Excel VBA Function Not Finding Exact Match in Array

Asked Oct 01 '17 at 20:12

Active Oct 01 '17 at 20:12

Viewed 60 times

I have an array that contains "Hot Soup". When I use the following function in an If statement to test whether "Soup" is found in the array, it returns as true:

Function FindInArray(StringToBeFound As Variant, ThisArray As Variant) As Boolean

  FindInArray = (UBound(Filter(ThisArray, StringToBeFound)) > -1)

End Function

A test:

Sub Test()

    Dim bool As Boolean

    myArray = Array("Hot Soup", "Vanilla")
    bool = FindInArray("Soup", myArray)
    MsgBox bool

End Sub

This returns true, and it is not. Is there a way to return true ONLY if the match is exact?

Thanks!

asked Oct 01 '17 at 20:12

You may need to iterate through the resulting array of the filter and check for exact match. – Scott Craner Oct 01 '17 at 20:16
Aside from those in the question you can use this in your function `FindInArray = Not IsError(Application.Match(StringToBeFound, ThisArray, 0))` instead of what you have. – Scott Craner Oct 01 '17 at 20:22
^^^ The above only works with a single dimension array. – Scott Craner Oct 01 '17 at 20:23
you only need one line to display the behaviour .... `MsgBox Filter(Array("Hot Soup", "Vanilla"), "Soup")(0)` – jsotola Oct 01 '17 at 20:58
Scott Craner - thanks! If you put your Application.Match option as an answer I will vote it as correct. Thanks again! – Oct 01 '17 at 21:24
jsotola - that was unhelpful. – Oct 01 '17 at 21:25

Excel VBA Function Not Finding Exact Match in Array

0 Answers0