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.

 

[Kyle123]

Filter works on partial matches, so "a" is in the final array in the form of "a-b". Your second to last example works correctly for me

 

[Harry Flashman]

Filter works on partial matches, so "a" is in the final array in the form of "a-b". Your second to last example works correctly for me

If Filter works on partial matches then that is where I am going wrong. Can you (or anyone else) suggest an alternate method of checking whether an item is in an array?

Edit: yes you are write about my second last example. That actually does work correctly. I got that example the wrong way around. But I think people should be able to get my drift.

 

[Kyle123]

Perhaps more resilient, but not fool proof:

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

Though it's worth mentioning, that looping through arrays in memory is pretty fast, so shouldn't be ruled out. These approaches also will only work on arrays with a single dimension - they fail in the case of a 2d array which are very common in Excel

 

[pgc01]

Hi Harry

Filter does not perform an exact match.

Another option:

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

 

[snb_]

Sub M_snb()
    sn = Array("aa1", "aa2", "bb3")
    
    c00 = "aa1"
    c01 = "b"
    c02 = "Exact match: "
    
    MsgBox c02 & Format(InStr("~" & Join(sn, "~|~") & "~", "~" & c00 & "~"), "yes/no")
    MsgBox c02 & Format(InStr("~" & Join(sn, "~|~") & "~", "~" & c01 & "~"), "yes/no")

    MsgBox c02 & Format(UBound(Filter(Split("~" & Join(sn, "~|~") & "~", "|"), "~" & c00 & "~")) > -1, "yes/no")
    MsgBox c02 & Format(UBound(Filter(Split("~" & Join(sn, "~|~") & "~", "|"), "~" & c01 & "~")) > -1, "yes/no")

    MsgBox c02 & Format(Not IsError(Application.Match(c00, sn, 0)), "yes/no")
    MsgBox c02 & Format(Not IsError(Application.Match(c01, sn, 0)), "yes/no")
End Sub

 

[Harry Flashman]

Hi Harry

Filter does not perform an exact match.

Another option:

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

Thank you that was exactly what I was looking for.

This problem has a small piece of the jigsaw puzzle that I posted in this thread:
http://www.mrexcel.com/forum/excel-...-value-exists-new-collection.html#post3561227

Which was itself a small piece in a larger jigsaw puzzle.

My original question was how could I check if an item was in a New Collection, which I never figured out. So I thought I would try passing my New Collection to an array and find someway to check that.

Thanks to Kyle123 and snb_ as well for their input too. Much appreciated. I am teaching myself VBA from books and I no one at my work knows anything about it, thus the learning process involves a lot of persistence and trial and error. So I am really grateful for the pointers I get from this forum.

As it happens I am quite glad that I now have two methods at my disposal, and for the larger problem that I am working finding partial matches will also be very useful, and I will ultimately incorporate both approaches in my code. Cheers.

 

[snb_]

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

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[Rick Rothstein]

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