VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

Re VBA 1 – dimensional Horizontal and Vertical Array conventions…( ha 1, 2, 3, 4 )

Hi, … here we go, “ Ha – 1, 2 , 3, 4 “

. While answering this Thread yesterday..
http://www.excelforum.com/excel-prog...ing-macro.html
.. some basic 1 - dimensional Horizontal and Vertical Array conventions bugged me a bit.. I got over it but do not quite understand what is going on. Can someone help me get it clear.

. Basically I am slightly confused with array formats of this type
. (1,1) (1,2) (1,3) (1,4)
. compared with those of this type…
. (1) (2) (3) (4)
.
. The problem came up whilst concatenating a row of cells using the Visual Basic Application Join Method. I needed this format
. (1) (2) (3) (4)
. for the first argument, rather than this format
. (1,1) (1,2) (1,3) (1,4)
. I cannot quite see the subtle difference.
……….

. To demonstrate pictorially. Say I have the following spreadsheet before running any code, with two arbitrary I dimensional ranges vertical ( A1:A4) and horizontal (A1:D1):
l
Using Excel 2007

-	A	B	C	D
1	HAone	Htwo	Hthree	Hfour
2	Atwo
3	Athree
4	Afour

Tabelle1

I wish to concatenate the cell values from the vertical and Horizontal cells ranges into arbitrary cells, say, for example, the horizontal range in to be put in cell B5 and the Vertical range to be put in cell A6. - So after running my code I wish to have this:

Using Excel 2007

-	A	B	C	D
1	HAone	Htwo	Hthree	Hfour
2	Atwo
3	Athree
4	Afour
5		HAone Htwo Hthree Hfour
6	HAone Atwo Athree Afour

Tabelle1

. By a bit of googling and experimenting I wrote a code to achieve this:

Sub VerticalHorizontalArrays()
10    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Tabelle1")
    
20    Dim varArrayH() As Variant, varArrayV() As Variant 'Dynamic Arrays for "Capture" of Spreadsheet Horizontal and Vertical Ranges
30    Dim varTransposeV() As Variant, varArrayH2() As Variant 'Dynamic Arrays for modified 1 dimensional format (  (1) (2) (3) (4)  )

'Use typical one line capture possibility to get Spreadsheet Range cell values into a VBA Array
40    varArrayH() = ws.Range("A1:D1") 'Format type (1,1) (1,2) (1,3) (1,4)
50    varArrayV() = ws.Range("A1:A4") 'Format type (1,1) (2,1) (3,1) (4,1)
    
'Transpose the Array for the Vertical Array to get it horizontal, then use join function to paste into A6 the concatenated values from the 4 cells
60    Let varTransposeV() = Application.WorksheetFunction.Transpose(varArrayV) 'Returns format type (1) (2) (3) (4)
70    Let ws.Range("A6").Value = Join(varTransposeV, " ") 'Works. Join Function concatenates values in the first argument (Array, seperated by the second argument ( one space here ) which produces a variant  ( of type string in this case ) whicj is then diirectly pasted into cell A6.
   
'Change the format of the Horizontal Array so that it has a format apprpriate for the Join Function
80    'Let ws.Range("B5").Value = Join(varArrayH(), " ") ' Don't work - wrong 1 - dimensional array format!!
90    Let varArrayH2 = Application.WorksheetFunction.Index(varArrayH, 1, 0) 'Returns format type (1) (2) (3) (4) >> Index Function with third argument (column co - ordinate) set to 0 will return the entire row given by second argument ( row - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)
100   Let ws.Range("B5").Value = Join(varArrayH2(), " ") 'Works

'Just for fun confirm that I can paste out both versions of the Horizontal Array to a cell...
110   Let ws.Range("A11:D11").Value = varArrayH() '.... using the VBA allowed "one liner" to assign values ...
120   Let ws.Range("A12:D12").Value = varArrayH2() '.. in an Array to cells in a range
End Sub 'VerticalHorizontalArrays()

.. Using F8 and the setting watches in the watch window for all arrays in the code I was able to observe the different array formats, and so “empirically” see the format requirements to get my code to work.

. I can clearly see the form of the syntax requirement, and see for example that “luckily” the transpose function returns me the correct syntax applied to a Vertical array. ( (1) (2) (3) (4) type format ).

. For the horizontal array I see initially it has the incorrect ( (1,1) (1,2) (1,3) (1,4)
Type ) format. So I do a “Trick” to change the format using the Index function (- This “Trick” is usually used to “slice” ( return a single row or column ) from a multi- dimensional array
( .. seefor example:-
https://usefulgyaan.wordpress.com/20...ication-index/
.. ) ) .
. The changed format gives then the required (1) (2) (3) (4) type format
…so far so good
BUT:-
… I do not really understand the subtle difference in the two versions of a 1- dimensional (horizontal ) array.

. can anyone explain the subtle difference in plain English ( or German! )


Thanks
Alan

P.s.1 “Just for fun” at the end of my code I paste out both format versions of the horizontal array to
Arbitrary Ranges using the VBA allowed “one liner” to assign values in an Array to cells in a range. The results are identical for both Arrays

P.s.2 I upload the file I am using for these experiments in case it helps:
https://app.box.com/s/h6lyxjmh84det1j15rhgxz01ectwjyma