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
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
c) be used to create Ranges, eg for use as Dynamic Named Ranges
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:
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:
The same can be applied for a given "row" - say the 10th row of our Array
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:
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:
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:
that's super clever !!! especially when you consider the "native" INDEX equivalent:
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
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.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by dangelor
Bookmarks