VBA issue finding string in a string array when in fact it is a part of the array

Question

I am confused about this case I came across. I Have a code:

Sub Testing()

Dim UnitArray
UnitArray = Array("Standard", "Jumbo")

Dim Unit As String
Unit = "Standard"

If IsInArray(UnitArray, Unit) Then
MsgBox ("Great success")
End If

End Sub

And a function:

Function IsInArray(arr As Variant, myVal As Variant) As Boolean

   Dim mtch
   mtch = Application.Match(myVal, arr, True)
   If Not IsError(mtch) Then
     If mtch = UBound(arr) Then
        If arr(UBound(arr)) = myVal Then IsInArray = True: Exit Function
     Else
        IsInArray = True: Exit Function
     End If
   End If
   IsInArray = False
   
End Function

If that array has only "Standard" - it works, but else it doesn't find the value. What's the mistake I have made?

I cannot understand your question, sorry. You posted a function I supplied to another question placed by you. Now, how do you test it for the second initial array element? Did you try `If IsInArray(UnitArray, "Jumbo") Then` and it does not return `True`? "I am confused" does not let us understand what confusion are you talking about... What did you try, what were your expectations against the return? — FaneDuru, Jan 20 '22 at 14:10
@FaneDuru - the problem is that currently `If mtch = UBound(arr) Then` is `True` because `arr` is 0-based but `Match` is 1-based. So it appears that `"Standard"` is not in the array. But really, this function is overkill for what OP wants to do. — BigBen, Jan 20 '22 at 14:12
Also `Match` is much slower than looping the array elements. https://stackoverflow.com/questions/18754096/matching-values-in-string-array — Warcupine, Jan 20 '22 at 14:14
The above function has been designed to work for `Integers`, if I remember well. Only in such a case `Match` may return the last array element **if not a match**. For strings, it should return a match, even if the array is 1D or 2D type... — FaneDuru, Jan 20 '22 at 14:15
@FaneDuru - I guess OP is looking for exact match too. `mtch = Application.Match(myVal, arr, 0)` probably. — BigBen, Jan 20 '22 at 14:16
@BigBen That's why I asked him to better describe his 'confusion'... You may be right, I agree, but this looks something subliminal for me. Like you know, English is not my strong point... :) `IsInArray` does exactly what its name suggests. It only returns `True` if a match exist. I will post a simpler piece of code able to deal with strings... — FaneDuru, Jan 20 '22 at 14:17

score 1 · Accepted Answer · answered Jan 20 '22 at 14:28

You can test a strings array in a even simpler way. The function you try using has been designed for Integers array...

Sub Testing()
Dim UnitArray
UnitArray = Array("Standard", "Jumbo")

Dim Unit As String
Unit = "Standard"
Dim mtch
'mtch = Application.match(Unit, UnitArray, 0)
mtch = Application.match("Jumbo", UnitArray, 0)
If Not IsError(mtch) Then
    MsgBox ("Great success")
End If

Like you can see, it correctly returns for any array element... Commenting/uncommenting the code lines starting with mtch will return a match for any of the array elements.

VBA issue finding string in a string array when in fact it is a part of the array

1 Answers1

Linked