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

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
– FaneDuru, Commented Jan 20, 2022 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
– BigBen, Commented Jan 20, 2022 at 14:12
Also Match is much slower than looping the array elements. stackoverflow.com/questions/18754096/… — Warcupine
– Warcupine, Commented Jan 20, 2022 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
– FaneDuru, Commented Jan 20, 2022 at 14:15
@FaneDuru - I guess OP is looking for exact match too. mtch = Application.Match(myVal, arr, 0) probably. — BigBen
– BigBen, Commented Jan 20, 2022 at 14:16

FaneDuru · Accepted Answer · 2022-01-20 14:28:42Z

1

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.

answered Jan 20, 2022 at 14:28

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related