snb, who wrote the detailed piece on dictionaries, has applied the same rigor to arrays.
I knew there would be something in there that I didn’t know (there was plenty). Under the section about identifying the existence of an item in an array, you can use a defined name:
6.2.2.3 The Excel-object ‘Names’
Assign the array to a Name’.
Excel creates an arrayformula, consisting of a string.
The item can be searched for in that string.
Embed the search string in quotation marks.
Embed numerical values in commas.Names.Add "snb_002", a_sn msgbox InStr(Names("snb_002").Value, """abcde""")>0 msgbox InStr(Names("snb_002").Value, ",1245,")>0
