A Daily Overdose of Arrays

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.

snb’s VBA for Smarties: Arrays

4 thoughts on “A Daily Overdose of Arrays

  1. 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.

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *