. This, I felt could well fit in a s a follow up in many Threads I have been in, for example
.. but they are a bit over used.. so here goes with a new one:
. (i) With one exception I never use or pass on code lines that I do not understand. The exception is the use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays.
. (i) I could not resist using it as I found it so useful and quick As A “One Liner” at producing an Output Array derived from an Input Array using a code line of this form
arrOut() = Application.Index(arrIn(), rws(), clms())
where the Arrays rws() and clms() contain a list of "row" and "column" Indices of the rows and columns in the Input Array required in the Output Array.
. As often I have got close to answering the question in carefully preparing my Thread.. But any confirmation of my ideas or further comments from anyone that knows or thinks they may know would be very grateful. Up until now I have had the impression that no one alive really knows for sure what is going on.
. 1 ) Main Question. How exactly is .Index working here.
. 2) Minor Question. Why doesn’t .WorksheetFunction.Index not work. ( Up until now most people have thought they must work identically ) ( The error comes at compilation being that the second “row” argument has an incompatible type )
. To elaborate and demonstrate the situation.
. Consider a simple 4 x 4 , ( “A1:D4” ) , Range which I capture to my Input Array arrIn(). The retuned Output Array arrOut() I paste out to some arbitrary Range whose Top Left Corner is cell F6
Using Excel 2007
Row\Col A B C D E F G H I J 1 A1 B1 C1 D1 1 2 A2 B2 C2 D2 2 3 A3 B3 C3 D3 3 4 A4 B4 C4 D4 4 5 1 2 3 4 clms()\rws() 6 7 8 9 10
. On and off I have done endless experiments on the above sheet..., ( a useful code which I developed to aid in this I share appended in the Test Area ...
I do my best to summarise, conventionally I give row and column indices in the r c sequence.: )( And Note a “Horizontal” Table can represents both a 1 dimensional Array or a 2 dimensional Array of 1 row for rws() or clms() : - up until now all results are similar choosing either of these as Arguments- That is to say results are never affected by whether any Array is One dimension or 2 dimension with row, be it rws() or clms() )
.. If I have Arrays of such indices: rws()-
2 3 4
1 2 3 4
then I obtain typically arrOut()=
A2 B3 C4 ###
.. It appears VBA attempts a pair wise Analysis Along the lines of how CSE Array formulas work ( giving an error for the non complete pair ) , but then chooses to do a “quasi ReDim Preserve and tack on the result of each Pair wise evaluation in a new column.
. But straight away we have an exception to this rule, the case of a 1 element Array in one argument
1 2 3 4
A3 B3 C3 D3
( .....This incidentally gives the same results as the classic Array “slicing” ideas which are more commonly known and discussed:
arrOut() = Application.Index(arrIn(), 3, 0)
.......... But with the advantage of the extra characteristic we find of this:
1 3 4
A3 C3 D3
. One of endless Interesting things to do is to try this combination ( Note a vertical Table represents a 2 dimensional 1 column Array for rws() or clms() ).
2 3 4
1 3 4
A2 C2 D2 A3 C3 D3 A4 C4 D4
... I see what is happening, can and have developed endless empirically derived rules to very effectively use these and endless further characteristics, but cannot see a clear explanation of how / why it is happening. As soon as I think I have a theory, some other experiment does not follow it!!. Why , for example does VBA “Re Dim Preserve” in the horizontal here ? . Have I “fooled it” into transposing it’s “tacking on” of the next row from “to the right” to “to the bottom”
. Thread space limitations ( and reader patience ) will not let me give more than a small posting of my experiments... just for fun This
2 3 4
1 2 3 4
A2 B3 C4 ###
.. Again a VBA doing the same “pair wise evaluation, but somehow “fooled into” taking on below rather than to the right??
.Has anyone any comments. Can anyone point me into clear documentation to these characteristic. ( Why does .WorksheetFunction.Index not share the characteristics )
P.s. No Rush I shall continue to give good Thread solutions using these characteristics , but I would very much like at some point to understand what I am doing
P.P.s. Just noticed that the Bl__dy thing will take Multidimensional Attays for rws() and clms(). Wish it hadn’t - It's gonna be a long night of experimenting