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.
Very useful, thanks.
One small thing to note on that page, is that an array declared as (4 to 20) has 17 elements, not 16. Similarly, (2 to 20) is 19 elements, not 18, etc.
It has been amended in the english page.
Thank you for scrutinizing !
Absolutely wonderful resource! Thank you so much. Bookmarked for daily reference.
I also learned a few things from that link (and no doubt will learn some more when I read through it properly).
The only thing I would add is that when reading an array from a spreadsheet range it is significantly faster to use .Value2, rather than .Value. There may be some times when .Value is better than ,Value2, but I have yet to find one.