Excel VBA - check if string is in array

[Harry Flashman]

I have a test to see if a text string is in an array that mostly works, but sometimes fails and I don't understand why. Would someone please look over my code and tell me where I have gone wrong?

The routine calls a Function IsInArray which returns a True/False value depending on whether the search term is in the array.

Sub TestFilterArray()
MyArray = Array("a", "b", "c")
If IsInArray("a", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

The above routine correctly gives the message "Yes! The item is in the array"

Sub TestFilterArray()
MyArray = Array("a", "b", "c")
If IsInArray("z", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

The above routine correctly gives the message "No! The item is not in the array"
So far so good.

But this is where it fails:

Sub TestFilterArray()
MyArray = Array("a", "b", "c")
If IsInArray("a & z", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

The above routine returns the message "Yes! The item is in the array" when I think it should return the message "No! The item is not in the array.

I am searching for the string "a & z" and as we can see the string "a & z" is NOT in the array.

In a similar vein the following also returns the wrong response:

Sub TestFilterArray()
MyArray = Array("a - b", "b", "c")
If IsInArray("a", MyArray) = False Then
    MsgBox "No! Item is not in the array"
Else
    MsgBox "Yes! Item is in the array"
End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

In this case the first item in my array is "a - b" (note that this is a text string ), but for some reason because my test value is "a" the routine things it must be in the array.

Thus I am thinking that the ampersand and dash symbols are somehow confusing my routine.

What is going wrong? How can I change my routine so that it correctly identifies which text strings are in my array?

Any help would be greatly appreciated.

 

[snb_]

@Rick

Can you have a look in your 'corner' ?

 

[Rick Rothstein]

@Rick

Can you have a look in your 'corner' ?

I know I have a couple of pending questions... which part of it are you specifically referring to?

 

[snb_]

Zis Wan:

UDF (user defined function) replacement for Excel's DATEDIF function - Page 2

 

[Harry Flashman]

Sub M_snb()
    On Error Resume Next
    With New Collection
      .Add "aaa", "bbb"
      x3 = .Item("bb1")
      If Err.Number <> 0 Then MsgBox "item doesn't exist"
    End With
End Sub

Thank you snb_ that is exactly the sort of answer I was looking for in my other thread about determining whether an item exists in a collection. Cheers.

 

[Harry Flashman]

As long as your 'arr' variable will always be a one-dimensional array, you could write your function this way...

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = InStr(Chr(1) & Join(arr, Chr(1)) & Chr(1), Chr(1) & stringToBeFound & Chr(1)) > 0
End Function

Thank you Rick that also works well. Is there an advantage to that method over this approach?

IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))

Also I am curious about your method's construction. I get the idea of search for a string as a way of determining whether there is a match. And I sort of understand the need for the additional characters other wise a partial match would also return true, but I don't quite understand how particular arrangement returns True for an exact match.

What is the purpose of Chr(1)? Is it some kind of wildcard? And what is the purpose of this bit?

Join(Arr,Chr(1))

 

[Rick Rothstein]

Thank you Rick that also works well. Is there an advantage to that method over this approach?

IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))

QUOTE]
I usually try to work wholly with native VB function when possible (unless doing so will make the code obnoxiously long)... Application.Match is a call out to the Excel worksheet MATCH function. Advantage? I don't know... sometime calling out to the worksheet for something tends to be slower than using native VB functions, but sometimes not... I don't know if that is the case with the MATCH function or not.

Also I am curious about your method's construction. I get the idea of search for a string as a way of determining whether there is a match. And I sort of understand the need for the additional characters other wise a partial match would also return true, but I don't quite understand how particular arrangement returns True for an exact match.

The InStr function returns the character position of one string within another... if the string being tested for inclusion is not in the larger string, then InStr returns 0. If you notice the "greater than zero" test at the end of the code line... that is what produces the True or False Boolean result.

What is the purpose of Chr(1)? Is it some kind of wildcard?

No, it simply a character guaranteed not to ever be located in any of your text strings... that way, when affixed to the ends of the smaller text string, it cannot accidentally match any smaller part of the larger text string other than one having Chr(1) character on either side of it with identical characters between it.

What is the purpose of Chr(1)? Is it some kind of wildcard? And what is the purpose of this bit?

Join(Arr,Chr(1))

Join takes two arguments... a one-dimensional array and a delimiter... it then concatenates each array element together placing the delimiter between them... that is how I make sure the exact match takes place by insuring each thing to be matched has a Chr(1) character on either side of it.

 

[Harry Flashman]

Thanks for the in-depth explanation Rick. That seems to all make sense. I'll play around with it to make sure I properly understand. Cheers.