Thread: VBA: Working with "Areas" within 2d Arrays

Of late I have become more and more convinced that Index is the best "value for money" Worksheet Function in Excel.

In worksheet function form the INDEX function can for example do the following (all context aside):

a) return single Values

=INDEX($A:$A,4)

note: here the above acts like INDIRECT in so far as if row 4 is deleted the above would still return contents of the "new" A4 and not error
(we are for sake of simplicity assuming Col A is not deleted in above example)

b) return Array of Values for use in conjunction with other functions

=SUMIF($A$2:$A$10,"Apple",INDEX($B$2:$Z$10,0,MATCH("values",$B$1:$Z$1,0)))

c) be used to create Ranges, eg for use as Dynamic Named Ranges

Name: _PivotData
RefersTo: =$A$1:INDEX($A:$IV,COUNTA($A:$A),COUNTA($1:$1))

which all in all makes it incredibly flexible and it does all of this whilst being non-volatile (post XL97) unlike equivalent function types (eg OFFSET, INDIRECT etc...)

However, further to it's "native" flexibility it's also very useful in VBA specifically when working with Arrays.

Let's assume we have a numeric 2d Array which for sake of "alternatives" is not sourced from a Range object.

Using a very basic (and admittedly unrealistic) set up along the lines of:

Public Sub Example()
    Dim lngY As Long, lngX As Long
    Dim MyArray(1 To 50000, 1 To 4) As Double
    For lngY = LBound(MyArray, 1) To UBound(MyArray, 1) Step 1
        For lngX = LBound(MyArray, 2) To UBound(MyArray, 2) Step 1
            MyArray(lngY, lngX) = Rnd
        Next lngX
    Next lngY
End Sub

Let's now assume we want to Sum the contents of the 2nd "Column" of our 2d Array without having to iterate each "item", how can we do this ?

INDEX offers us a very simple method:

Debug.Print Application.Sum(Application.Index(MyArray,0,2))

The same can be applied for a given "row" - say the 10th row of our Array

Debug.Print Application.Sum(Application.Index(MyArray,10,0))

This is very handy but what if we wanted to sum an area within our Array - say from "row" 50 to "row" 15000 (inclusive) whilst omitting "columns" 1 & 4 ?

In terms of the native function and a range this is relatively straightfoward, if we assume our 50000 x 4 matrix is A1:D50000 then:

=SUM(INDEX(A:D,50,2):INDEX(A:D,15000,3))

or

=SUM(INDEX(B:B,50):INDEX(C:C,15000))

but how can we reflect the same in VBA using INDEX ?

Previously I thought this was not possible... then along came our very own lecxe with this very clever trick:

Debug.Print Application.Sum(Application.Index(MyArray, Evaluate("ROW(50:15000)"), Array(2, 3)))

note: you could use shorthand for the Evaluate if preferred - ie [row(50:15000)]

That's pretty darn clever !!!


Better yet this technique can handle non contiguous parts of the source array - say we wanted the same rows but to sum columns 1 & 4 rather than 2 & 3:

Debug.Print Application.Sum(Application.Index(MyArray, Evaluate("ROW(50:15000)"), Array(1, 4)))

that's super clever !!! especially when you consider the "native" INDEX equivalent:

=SUM(INDEX(A:A,50):INDEX(A:A,15000),INDEX(D:D,50):INDEX(D:D,15000))

Better still (is this even possible?!) pending actions we could even revert the ordering of the "columns"

So let's now assume that we have a 5th Column in our Array which is populated with strings which may or may not repeat ("apple", "banana" etc)

We now want to match "apple" in between "rows" 50 and 15000 in our Array and return the value associated with that match from "Column" 3

Using the above technique we could create a right to left VLOOKUP using this sub array technique

Debug.Print Application.VLookup("apple", Application.Index(MyArray, Evaluate("ROW(50:15000)"), Array(5, 3)), 2, 0)

If you feel you must reply to the above (not expected) please note the following:

a) the above is not a question

b) none of the techniques outlined above are being advocated as "best practice"

c) the set ups above are entirely hypothetical

Following on from the above:

though there are countless alternative methods for all of the above they it is not the intention to discuss them here - this post is merely to demonstrate lecxe's Evaluate w/INDEX trick.


The post in which this trick was first brought to my attention resides a) on another Excel Message Board and b) within a restricted forum of that Message Board so at this stage I can not provide a direct link unfortunately.

@lecxe, thanks for this great post - I trust others who find it interesting will "tip your scales" accordingly.

One final use for Index in VBA (which lecxe kindly reminded me of) is something I used earlier today, namely:

circumventing the need for Transpose when creating 1d Arrays from Horizontal Range Vectors:

Dim vArray As Variant
vArray = Application.Index(Range("A1:J1").Value,1,0)