This is post https://www.excelfox.com/forum/showt...ll=1#post23287
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23287&viewfull=1#post23287
Use in Application.Index with Look Up Rows and Columns Arguments as VBA Arrays,
arrOut() = Application.Index(arrIn(), rws(), clms()),
https://www.excelforum.com/excel-new...vba-arrays.htm
-
2DarrOut() = Application.Index1D(arrIn(), rws(), clms())
arr1D - arr2D
arr1D() – arr2D() using Index Function
This application idea is very relevant even if at first glance not quite so…
The application will only be described briefly here.
Brief Application explanation
We can explain/ demonstrate ( Examples https://www.excelforum.com/excel-new...vba-arrays.htm
https://www.excelfox.com/forum/showt...ex-application)
https://eileenslounge.com/viewtopic....yDoeky#p265384
https://www.excelfox.com/forum/showt...ll=1#post12072 ) that sometime in Excel and VBA, Excel Index functions when supplied arrays as the second/third row/column argument , ( instead of the more typical single row, and single column pair ) will not error, but rather give an output that has the dimensions of the row and column arrays, and the values as determined by the elements of those row and column arrays.
Excel Spreadsheet example
The full discussion of this is discussed in detail at the other links, but summarised, the macro below will give us something like this
a b = Index( { a b c d } , { 1 1 , { 1 2 ) c d 1 1 } 3 4 }
In other words the 1x4 1 dimensional array, (or 1 "Row” array),
{a, b, c, d}
, is converted to a 2x2 2 dimensional array,
{a, b
c, d}
The final dimension is determined by the dimensions of the row and column element arrays, and the final values are determined by looking at the co ordinate pairs applied to the 1 D source / input array , in the typical convention of along all columns in a row , then the same for the next row and so on. The position in the final array mirrors the sequence
The demonstration is principally an Excel spreadsheet example, and we could do it all manually, entering in the various formulas by hand. However this is often tricky, since anything involving separator characters and the such ( . , ; / \ ) are often typical to get correct syntaxly since there are often different land conventions and / or various setting involving these characters can affect the locally used syntax.Code:Sub ArraysInExcel2() ' https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285 Cells.ClearContents Range("C1").Value = " = Index( {": Range("H1").Value = "} , { ": Range("K1").Value = " , { ": Range("K2").Value = "} ": Range("N1").Value = " )": Range("N2").Value = "} " ' Some formating to help explanation of Pseudo coding Range("D1:G1,I1:J2,L1:M2,A1:B2").ClearContents ' data range Let Range("D1:G1").FormulaArray = "={""a"",""b"",""c"",""d""}" Debug.Print Range("D1:G1").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("D1:G1").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("D1:G1").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) ' Rws() Clms() ItemIndicies Let Range("I1:J2").FormulaArray = "={1, 1;1, 1}" Debug.Print Range("I1:J2").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("I1:J2").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("I1:J2").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) Let Range("L1:M2").FormulaArray = "={1, 2;3, 4}" Debug.Print Range("L1:M2").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("L1:M2").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("L1:M2").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) ' Index array formulas Let Range("A1:B2").FormulaArray = "=INDEX(D1:G1,I1:J2,L1:M2)" Debug.Print Range("A1").FormulaArray ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("A1").Formula ' =INDEX(D1:G1,I1:J2,L1:M2) Debug.Print Range("A1").FormulaLocal ' =INDEX(D1:G1;I1:J2;L1:M2) Let Range("A1:B2").FormulaArray = "=INDEX({""a"",""b"",""c"",""d""},{1, 1;1, 1},{1, 2;3, 4})" ' https://i.postimg.cc/dtVybDs5/Index-arr1-D-to-arr2-D.jpg Debug.Print Range("A1").FormulaArray ' =INDEX({"a","b","c","d"},{1,1;1,1},{1,2;3,4}) Debug.Print Range("A1").Formula ' =INDEX({"a","b","c","d"},{1,1;1,1},{1,2;3,4}) Debug.Print Range("A1").FormulaLocal ' =INDEX({"a"."b"."c"."d"};{1.1;1.1};{1.2;3.4}) 'End Sub
VBA is however consistent: a column separator in an array is shown as a , and a row separator as a ; so {a, b ; c, d ; e, f} ), is a 2 column x 3 row array,
{a, b
c, d
e, f}
So it is often a good idea to use short simple coding to put values and formulas in. Excel may change their form appropriately to suit the local syntax. In the 'green comments , the Debug.Print results that you get may vary for the case of the .FormulaLocal case
For the relevance to this thread we concentrate on getting the third column argument. ( Because of interception and intersection we can reduce the second row argument to just a single 1 , since Excel effectively extends that single value to the missing elements to extend it effectively to a 2x2 array with all values of the single value, 1 in this case)
Code:Sub ThirdColumnArgument() Dim vTemp As Variant Let Range("A4:C5").FormulaArray = "=ROW(1:2)*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=(ROW(1:2)/ROW(1:2))*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=1*COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=COLUMN(A:C)" Let Range("A4:C5").FormulaArray = "=COLUMN(A:C)+((Row(1:2)-1)*3)" https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839tUQl_92mvg https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg.9isY3Ezhx4j9itQLuif26T https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg.9irSL7x4Moh9itTRqL7dQh https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdshr6 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ- https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9zXI https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_3f https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11nH https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOeXa https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpTG8 https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA End Sub
.FormulaArray
For this Thread post, we will just accept that the .FormulaArray is required to tell Excel that we want to put array values across a range more than one cell and that we want to see them . The full explanation of this is a very large subject discussed elsewhere involving amongst other things the whole CSE story
Reply With Quote
Bookmarks