Hi
A follow up explanation and discussion of the “One Liner method” from Post #8, #10, #11, # 12
We are talking about using a single code line of the following form.......
______arrOut() = Application.Index(arrIn(), rws(), clms())
____....to achieve an Output Array
arrOut(), based on a combination of an Input Array
arrIn() ( sometimes referred to as a
Grid or
Matrix or
2D structure ("rows" x "columns"). ), and
rws() and
clms() which will typically contain a list of "row" and "column" Indices.
_ 1) Basic Simple Form.
Right at the outset a very fundamental point I did not grasp for a long time as I never saw it documented anywhere, is that in the “simplest basic form” VBA pairs up the
rws() and
clms() indices and returns a
( 1 Dimensional ) Array of Elements .Those Elements are those at the
intersect of the indices Pairs.
For this discussion we will note that we can replace
arrIn() with a particular Worksheet, ( say
Dim ws as Worksheet:
Set ws=Worksheets(“Sheet1”) ), either using
__arrIn()=ws.Cells.Value
or simply referring to the entire Worksheet Range with
__ws.Cells ( noting that by default a reference to a Cell in the Cells Range will return the value in the cell )
The latter version has various advantages that we will discuss later and so we will stick with that for now
###
______arrOut() = Application.Index(ws.Cells, rws(), clms())
Coming back to the “simplest basic form” where i am specifically referring to 1 Dimensional Arrays for the indicies, for example
___rws() = Array(1, 2, 3, 4)
___clms()=Array(1, 2, 3, 3)
____here
arrOut() will be a 1 D Array with the values given in the cells A1
__B2
___C3
__and
__C4 for convenience call these values
__vA1
___vB2
___vC3
__vC4
_ 1b) Application Example type to similar to this Thread.
To keep a bit in context. A similar practical example that along the way will again show the neat trick demonstrated y the pgc code to get easily all the required indices. The example will be reduced significantly in size which is always better to do in a Forum Threads anyway to solve a real life problem that may be much bigger, but only by duplicating things such as rows and columns : For demonstration purposes a reduced row and column size makes the thread mot readable, and usually a code will be easily adaptable to a larger amount of data.. So let’s say we want to chop out the first 4 column values in the second two rows so that our Output Array takes a form such as
___arrOut() = vB1
__vB2
__vB3
__vB4
__vC1
__vC2
__vC3
__vC4
___( Note we are referring here to the values in the given Cells, not the Range Objects
The code line required would be of this form
arrOut()=Application.Index(ws.Cells, Array(2, 2, 2, 2, 3, 3, 3, 3), Array(1, 2, 3, 4, 1, 2, 3, 4) )
following along the discussions of Post #11, we can get these required indices easier than just tying them all in: Often for convenience typical Worksheet Formulas are used which in VBA Code we can do by use of the VBA Evaluate Method, which in its simplest form and definition does just that, that is to say evaluates or “does” what Excel does after hitting Enter in a cell with some mathematical expression in it.
Clms()
Here starting with
___Evaluate("=column(A:H)") returning 1, 2, 3, 4, 5, 6 ,7, 8
The above and the next few lines hit a bit of a messy subject, that to do with how Excel deals with Formulas that can, cannot, and can be coerced into giving an Array of values rather than a single value. The first formula is fairly well behaved and in VBA the above line does indeed return a data Field ( of Variant types as this is the returned type of Evaluate ) containing the long Numbers as above.
Hence this code lines gives us no problem and “creates” our column indicia Array
Dim clms() As Variant:Let clms() = Evaluate("=column(A:H)"). In a spreadsheet we would do a form of the
___C
rappy S
h_tty E
xpression Stuff , whereby we
___highlight a Row of 8 cells,
___ Hit F2,
__ paste the Formula in the Formula bar,
__ and finally the famous ..Hit
__Ctrl + Shift + Enter. This basically tells Excel we have Arrays in our formula, and (
for this second Type of C S E , distinguished by selecting a number of cells rather than just one in the first tyoe of C S E ) it also assigns where we want to paste out the final evaluated Array values
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]1
[/td][td]2
[/td][td]3
[/td][td]4
[/td][td]5
[/td][td]6
[/td][td]7
[/td][td]8
[/td][/tr]
[/table]
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][td]=COLUMN(A:H)
[/td][/tr]
[/table]
_ we need to do a bit of Maths to get the actual indicia we require, such as that given by pgc
___ = mod(column(A:H),4) = 1, 2, 3, 0, 1, 2, 3, 0
’__mod is “bit left over” after taking away as many 4’s as ( if ) possible
___ = mod(column(A:H)-1,4) = 0, 1, 2, 3, 0, 1, 2, 3
___ = mod(column(A:H)-1,4)+1 = 1, 2, 3, 4, 1, 2, 3, 4
The
___C
rappy __S
h_tty E
xpression stuff gives us these values in the Spreadsheet as before
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]1
[/td][td]2
[/td][td]3
[/td][td]4
[/td][td]1
[/td][td]2
[/td][td]3
[/td][td]4
[/td][/tr]
[/table]
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][td]= MOD(COLUMN(A:H)-1,4)+1
[/td][/tr]
[/table]
But... At this point we hit a bit of a problem with VBA here. We can demo this by setting a Variant variable equal to the Evaluated Formula instead of the Array variable thus
Dim vTemp As Variant:
Let vTemp = Evaluate("=mod(column(A:H),4)") = 1
This returns a long number. It is the same as one gets when one just write the formula in one cell or writes the formula in all cells without doing all the
___C
rappy Sh_tty Expression stuff. No one person seems privy to exactly what the problem is and why these things sometimes do work and sometimes not. But to cut a long story and a couple of long Threads short
Multiple Columns Into Single Column Using Data Text To Column
http://www.mrexcel.com/forum/excel-...ons-evaluate-range-vlookup-2.html#post3946289
One can co erce VBA into giving out, that is to say returning via the Evaluate method by typically including an extra part at the start of the formula. An
____=If(Column(A:
__),
_____mod(column(A:H),4)
____ )
Is a nice one that could allow some additional manipulation of the columns, but there are infinite possibilities... just a few shown here:
Rich (BB code):
Sub clms()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence
Dim clms() As Variant: Let clms() = Evaluate("=column(A:H)") ' Returns 1, 2, 3, 4, 5, 6, 7, 8
Dim vTemp As Variant: Let vTemp = Evaluate("=mod(column(A:H),4)") 'Retuns Long Number 1
Let clms() = Evaluate("=If(column(A:H),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, 0
Let clms() = Evaluate("=If(column(A:G),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, error
Let clms() = Evaluate("=If(column(),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, 0
Let clms() = Evaluate("=If(row(),mod(column(A:H)-1,4))") 'Returns 0, 1, 2, 3, 0, 1, 2, 3
Let clms() = Evaluate("=Index((mod(column(A:H)-1,4)+1),)") 'Returns 1, 2, 3, 4, 1, 2, 3, 4
End Sub
_....................................................
rws()
For our rws() some similar maths is needed as well as a coercing bit. The following takes us through some typical steps, we use the Integer ( Int ) here, again a suggested mehtod form pgc
Posts #9 and Posts #11
Rich (BB code):
Sub rws()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence
Dim rws() As Variant: Let rws() = Evaluate("=column(A:H)") ' Returns 1, 2, 3, 4, 5, 6, 7, 8
Let rws() = Evaluate("=Index((int(column(A:H)/4)),)") 'Returns 0, 0, 0, 1, 1, 1, 1, 2
Let rws() = Evaluate("=Index((int((column(A:H)-1)/4)),)") 'Returns 0, 0, 0, 0, 1, 1, 1, 1
Let rws() = Evaluate("=Index(((int((column(A:H)-1)/4))+1),)") 'returns 1, 1, 1, 1, 2, 2, 2, 2
End Sub
_ ............................................................................................
_
2 ) Advanced form
The OP actually required a "vertical Output". This was achieved through various code solutions using a simple Transpose of the Array discussed in
_ 1) . But here we are discussing the "neat one liner" solution, and require some more in depth discussions in how intimately and internally Excel is working..
It would appear that when
VBA ( or
Excel in general ) does operations requiring tying up of positions of stuff based on co ordinates, that there is some form of default "
intersection searching" going on.
http://www.mrexcel.com/forum/excel-...ket%94-you-have-input-array.html?#post4049222
and in parallel we can influence this a bit sometimes by various Methods.
Consider the last experiment but with a reduced number of “column” indicia, specifically the 1, 2, 3, 4 obtained for example with
Pooclms() = Evaluate("=column(A:D)")’ Returns 1, 2, 3, 4
Consider further reducing the
Poorws() = Evaluate("=column(B:C)")’ Returns 2, 3
this returns us
__vB2
____vC2
____#NV
____#NV
Currently both
rws() and
clms() are 1 Dimensional Arrays (
Experiments show that with 2 Dimensional 1 “row” Arrays the results are identical )
My suggestion would be that Excel “shoots out” “straight” lines:
___ “down” from the first argument (
rws() ) and ;
____ “across” from the second argument (
clms() ). An aside observation that may give some support to this idea. It was found here
Trouble writing huge array into worksheet range [SOLVED]
that strangely assigning the Elements of a 1 D Array to a “vertical” Spreadsheet Range resulted in the entire ( “1 column ” ) Range to be filled with the first value in that 1 D Array. (
For the case of a similar experiment with a 2 D 1 “column” Array or a 2 D 1 “row” Array , the results were more as expected. That is to say trying to assign in the wrong “orientation” results in just getting the Top Left corner filled. ) This could be telling us that a 1 D Array has a particular meaning inside
VBA or maybe the computer itself, and only under certain conditions is “taken” as a Pseudo “Horizontal” Array. Possibly somehow supports my idea that
Excel “shoots” out “lines” from the Array Elements of 1 D Arrays. (
or 2 D Arrays being lookes at as 1 D arrays## )
At this point it should also be noted that the following “Tricks” do not work for the Application.WorksheetFunction.Index
Trouble writing huge array into worksheet range [SOLVED]
This could suggests we are “messing about” in the following with some complex ways in which Excel or the computer itself stores Arrays within Function such as .Match and .Index. So it is a co-incidence that it works. (
Note however point !!! )It does not seem to be supported by documentation, and maybe no one really knows what is going on, which worries me a bit in its usage..
Application.Index with Look Up Rows and Columns Arguments as VBA Arrays
But Anyways:
The intercepts of these “Lines” return Element positions. Where these “Elements” are held internally is a complex system based on offset positions to some reference point. The Index Function has a set of code lines to interpret these based on seeing “pseudo” Horizontal given arguments.
_ 2 a) Transpose rws()
Consider only the column indices 1, 2, 3, 4 and let us “mess about” with the “row” indices such that we use a similar Worksheet function , but that for the Spreadsheet Row
_____ rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
__.. which returns a 2 Dimensional 1 “column” Array. This is effectively the transpose of what the .Index was “expecting”. I would suggest it is “shooting” out lines similar to before for the “columns” ( just 4 of them instead of 8 of them this time. ). But i would suggest for “row” indicia 2 now a line “shoots” out in a “line” with a transposed orientation and hence intercepts now all the “column” indices. For “row” indicia 3 a similar “line” “shoots” out along the same “lines” or “track” that indicia 2 did – It sort of has the same position in what the index is “trying” to “look” at
## as a 1 D array argument. But by virtue of the transpose caused by
rws() being a 2 D 1 “column” Array it starts “set forward” along this track. So it has a different relative offset position in the Excel or Computer memory
Without knowing exactly what is going on deep down internally one can only guess, but a good guess might be that the index working with its normal Interpretation rules of the various offsets and intercepted points may return a 1 D Array of all columns for “row” indicia 2,
__ and do the same for “row” indicia 3 except “set” forward by an offset which will be interpreted as a “Row” offset. So a 2 D 4 “column” To 2 “row” would be returned such as this:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]vA2
[/td][td]vB2
[/td][td]vC2
[/td][td]vD2
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]vA3
[/td][td]vB3
[/td][td]vC3
[/td][td]vD3
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet:
IndexWithArraysWTF[/td][/tr][/table]
___for
arrOut() in such a code:
Rich (BB code):
Sub AppIndexRT23C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
Dim clms() As Variant: Let clms() = Evaluate("=column(A:D)") ' Returns 1, 2, 3, 4
Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub
Indeed it does!. I confess i already knew the answer. But I did since about 6 Months and this is the first time I have found any suggested Theory from anyone!
Application.Index with Look Up Rows and Columns Arguments as VBA Arrays
_ ...............................
2b) Transpose rws() and clms()
I think it is a reasonable argument now to say that if I now go on to Transpose the
clms() as well, I will be back to the situation of the “lines” being at right angles to each other and so only returning single intercept positions. But the offset caused by the two “identical” internally held “offsets” within the
VBA or computer memory are interpreted by the .Index to give a Transposed final output. Such that this code:
'
Rich (BB code):
Sub AppIndexRT23CT1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
Dim clms() As Variant: Let clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values 1 / 2 / 3 / 4
Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub
___will return this for arrOut()
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]vA2
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]vB3
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]error
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]error
[/td][/tr]
[/table]
_ this basic form meets the OP requirement when appropriate “row” and “column” indices are chosen.
_ ........
_ 2 c) Transpose only clms()
Just for completeness, we can explain the above scenario in words: “The column” indices “shoot” out along the same track but each with at a different “offset” start point. Each “column” “line” intercepts the both 2 “row” indices. Each “column” Line is therefore interpreted as an Array of 2 elements of all “row” elements for that “column” by the .Index function. But by virtue of the different “start” “offset” held within the VBA or Computer memory the .Index interprets that these Arrays are “stacked” on top of eachother, rather than tacked “at the end” to give a single “row”
So this code:
Rich (BB code):
'
Sub AppIndexR23CT1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("column(B:C)") 'Returns 1 D Array 2, 3
Dim clms() As Variant: Let clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values 1 / 2 / 3 / 4
Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub
___returns this for
arrOut()
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]vA2
[/td][td]vA3
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]vB2
[/td][td]vB3
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]vC2
[/td][td]vC3
[/td][/tr]
[tr=bgcolor:#FFFFFF][td]vD2
[/td][td]vD3
[/td][/tr]
[/table]
_ ................................................................
Some last thoughts:
_ ( i ) I cannot quite follow the logic that results in codes of the form below with the various variations or rws() argument return the following
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]vA2
[/td][td]vB2
[/td][td]vC2
[/td][td]vD2
[/td][/tr]
[/table]
_ ... Maybe the Application.Index “rows” and “column” argument are as Variant declared ( could not google this for Application.Index
Application.WorksheetFunction.Index is defined as Long for “row” and variant for “column” arguments – another thing preventing it from “working” with the “tricks” !!! ) ). Maybe then this somehow “shoots” differently such as to intercept all columns.
Rich (BB code):
Sub AppIndexR2_C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Array("2") 'Returns 1 D 1 Variant Element Array holding String value of 2
Let rws() = Array(2) 'Returns 1 D 1 Variant Element Array holding Long value of 2
Dim rws2(1 To 1, 1 To 1): Let rws2(1, 1) = "2"
Dim clms() As Variant: Let clms() = Evaluate("=column(A:D)") 'Returns 1 D 1 "row" Array 1, 2, 3, 4
Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
Let arrOut() = Application.Index(ws.Cells, 2, clms())
Let arrOut() = Application.Index(ws.Cells, rws2(), clms())
End Sub
_ ( ii ) The last example can also be achieved with the classic “Slicing” Technique....
https://usefulgyaan.wordpress.com/2...cing-an-array-without-loop-application-index/
____...with such a code:
'
Rich (BB code):
Sub AppIndexSptR2_C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim arrIn() As Variant: Let arrIn() = ws.Range("A1:D4").Value 'Restrict Input Array for split demo
Dim arrOut() As Variant
Let arrOut() = Application.Index(arrIn(), 2, 0)
End Sub
______... I suspect the argument
__0
__ somehow instruct VBA to take the argument as the full Array of all indicies
_ Hope this is / was a contribution to this Thread, and maybe the subject in general of creating Arrays from other Arrays. Maybe a last couple observation here. As Noted here:
http://www.mrexcel.com/forum/excel-...to-one-array-visual-basic-applications-3.html
Trouble writing huge array into worksheet range [SOLVED] - Page 2
_ A) The use of
.Cells as our first argument is not subject to the usually Array Limits.
###
_ B) The cells in a worksheet could be considered an enormous available ( for XL 2007 + ) “Array”
_ These two points return a nice way to do a consolidation of Arrays, even from Different Worksheets or Workbooks.
Firstly all Ranges would be brought into a main sheet with simple ( “pseudo” ) code lines
ArbritraryRangeInMainSheet.Value=EachRange.Value
Then a simple “one liner” based on careful selection of the Numbers in, and orientation of, the
rws() and
clms() Arrays as discussed in this Thread could be used to obtain a final Array of required form and size and orientation.
Alan
