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

[Doc.AElstein]

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:

Please Login or Register  to view this content.

.. 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

[mehmetcik]

Hi Alan,

As I see it the two forms are basically the same.

but typing (1,1) (1,2) (1,3) (1,4) would get boring after a while.

So I use application.transpose to transpose that form of array and end up with (1) (2) (3) (4).

Please Login or Register  to view this content.

[Doc.AElstein]

…As I see it the two forms are basically the same…..
..So I use application.transpose to transpose that form of array and end up with (1) (2) (3) (4)
...

Please Login or Register  to view this content.

Hi,
. Thanks for the quick reply. I agree they are basically the same.
. I follow your code. It is basically another attempt, I think, for my line 90

. However, if I am not mistaken, that code you gave does not actually return the simplified format ? At least it does not by me..(I am using XL 2007, Xl 2010 )
… MyArray in your code by observation in my watch window is a 2 row , 5 column Array (0 to 1, 0 to 4 ) . Correspondingly, by me, the first transpose gives a 5 row , 2 column array (1 to 5 , 1 to 2 ). The second transpose finally gives again a 2 row, 5 column Array ( 1 to 2 , 1 to 5 ), again of the non simplified form ( (1,1) (1,2) (1,3) (1,4) (1,5) )


… This code does by me what I think you were suggesting:

Please Login or Register  to view this content.

. This code by me gives initially a 1 row, 4 column array ( 0 to 0 , 0 to 3 ). The first transpose gives a 4 row 1 column array ( 1 to 4 , 1 to 1 ). This has the same format as my “Captured” vertical Range, varArrayV(). The second transpose then gives as, by my line 60 , an array of 4 columns of the simplified form ((1) (2) (3) (4) ) ( 1 to 4 )
……
… or alternatively this code also works similarly

Please Login or Register  to view this content.

. This code by me gives initially a 1 row, 4 column array ( 1 to 1 , 1 to 4 ). The first transpose gives a 4 row 1 column array ( 1 to 4 , 1 to 1 ). Again This has the same format as my “Captured” vertical Range, varArrayV(). And again The second transpose then gives as, by my line 60 , an array of 4 columns of the simplified form ((1) (2) (3) (4) ) ( 1 to 4 )




………………………


. But what is still bugging me is that there is some subtle difference. - as demonstrated in my example and experimenting - the Join Function would not allow the (1,1) (1,2) (1,3) (1,4) Format, as in my commented out line 80
. So I am still thinking about it a bit.
. But Many thanks for the reply, good to see someone has encountered this phenomena before..

Alan.

[romperstomper]

(1,1) (1,2) (1,3) (1,4)
is not a 1D array, it is 2D. Since Join, like Filter, requires a 1D array, you have to convert it.

If you assign a multiple cell range to an array/variant in one shot (using Value/Value2/Formula), you will always get a 2D array, never a 1D.

[Kyle123]

What Rory said, you are also confusing yourself since you missed the Option Base 1 in mehmetcik's code

[Doc.AElstein]

What Rory said, you are also confusing yourself since you missed the Option Base 1 in mehmetcik's code

Hi kyle

Oops, Correct...

Thanks


( Sorry mehmetcik ! )

[Doc.AElstein]

Hi Rory,

……If you assign a multiple cell range to an array/variant in one shot (using Value/Value2/Formula), you will always get a 2D array, never a 1D.

… yep, I thought that, but the confirmation from someone that knows for sure is always helpful..

……………………….

(1,1) (1,2) (1,3) (1,4)
is not a 1D array, it is 2D. Since Join, like Filter, requires a 1D array, you have to convert it.
……..

Ha …. 1 2 3 4
… I was thinking that for my next experiment. Thanks
. And I guess that the convention is that a 1 dimensional array is “quasi” horizontal??

… I have been experimenting again and modifying my code from post #1 I can get to paste out a vertical range by transposing a horizontal as in lines 130 and 140.
. But I am failing to get a 1 Dimensional Vertical array ( I think ) such as with by slicing a 2 dimensional 1 column array in line 160 or transposing a 1 dimensional “horizontal” array in lines 170 and 180 ???

Please Login or Register  to view this content.

. Can you confirm that the convention is that a 1 dimensional array is “quasi” horizontal.

. I thought somewhere along the lines I had encountered something equivalent to
= Array (“HAone” , “Htwo” , “Hthree” , “Hfour” ) as horizontal

Which in the vertical form had
= Array (“HAone” ; “Atwo” ; “Athree” ; “Afour” )
or
= Array (“HAone” / “Atwo” / “Athree” / “Afour” )
… but I can’t quite get it yet

Thanks
Alan

[romperstomper]

You can think of it that way in terms of trying to put it in a sheet, although a 1D array doesn't really have any direction.

Re your last point I think you are thinking of array constants in Excel formulas.

[Kyle123]

It makes no difference, you can think of arrays as columns and rows or rows and columns. There is no orientation.

The only time it makes a difference is when interacting with an Excel sheet which typically expect arrays with 2 dimensions (rows,columns)

You can however put data into a single row without it being a 2 dimensional array:

Please Login or Register  to view this content.

On a side issue, I suspect that you'll find looping through a 2 dimensional array and creating a new 1D array will be more efficient than Application.Transpose - especially on large arrays.

[Doc.AElstein]

Hi Rory,

You can think of it that way in terms of trying to put it in a sheet, although a 1D array doesn't really have any direction…...

Ok I think that clears that up, thanks.
. And that convention ties up that
I get a 1 dimensional array by slicing with index a 2 dimensional 1 row Array or by transposing a 2 dimensional array with 1 column
. whereas
I get a 2 dimensional array (with 1 column) if I slice with index a 1 column 2 dimensional array or transpose a 1 row 2 dimensional array

….

Re your last point I think you are thinking of array constants in Excel formulas.

… correct, - I was thinking that just now after remembering those endless ramblings I did in the test area pulling apart some of those Mega D___ B_____ Evaluate Range VLookUp Formulas you gave me…..

Many thanks again

Alan

[Doc.AElstein]

Hi Kyle,

It makes no difference, you can think of arrays as columns and rows or rows and columns. There is no orientation.

The only time it makes a difference is when interacting with an Excel sheet which typically expect arrays with 2 dimensions (rows,columns)…...

… I think I understand and agree with that, and I note my point / question that my code line 120 worked which happily took a 1 dimensional array,
.. which you have sort of cleared up … exactly as you said

…….

You can however put data into a single row without it being a 2 dimensional array:

Please Login or Register  to view this content.

…...

…. I did the above many times along the way as I tried get at my “Nutty 1 – Dimensional vertical Array idea”! I tried for example things of the form

Please Login or Register  to view this content.

…..

but they did not work



Variations of this did

Please Login or Register  to view this content.

…, suggesting that VBA has some sort of orientation convention for it’s 1 dimensional Array.

…….

On a side issue, I suspect that you'll find looping through a 2 dimensional array and creating a new 1D array will be more efficient than Application.Transpose - especially on large arrays.

Thanks that is a useful Tip, I seem to remember reading things along those lines in threads, and have been generally myself “going over” to capturing into array, doing everything there in VBA code, even sometimes with a bit complicated looping and then pasting back in one go rather than interacting with the spreadsheet or using various Application or Application.Worksheet functions and the like. The Array approach almost always seem to be quicker…..

Thanks again


Alan

[Kyle123]

suggesting that VBA has some sort of orientation convention for it’s 1 dimensional Array

No it doesn't, the Range object does however.

…. I did the above many times along the way as I tried get at my “Nutty 1 – Dimensional vertical Array idea”! I tried for example things of the form

I don't know what that means, quite simply you can put data into a row using a 1D array, to put data into columns/more than one row, you must use a 2D

Generally, worksheet functions are slow when used on arrays rather than in the worksheet, especially so on Variant arrays.

[Doc.AElstein]

No it doesn't, the Range object does however.
...........

Hmm, getting lost here... by saying VBA I think I could be referring to the Range object ( as “something to do with VBA” ? ), but thanks for making it may be a bit more precise..

.........
I don't know what that means, quite simply you can put data into a row using a 1D array, to put data into columns/more than one row, you must use a 2D......

.. That ties up with the 1 Dimension is "quasi" Row convention doesn't it.. It will only take a 1 dimensional Array into a row, but will insist on a 2 Dimensional Array ( albeit possibly with only 1 column ) for a vertical range?


.. I think I have got the general Idea thanks to your and Rory’ s help ???

Thanks again. I appreciate you coming back to what must appear to you somewot trivial.
. But it does help a Nut like me with limited experience ( and intelligence!!)

[Doc.AElstein]

- Having a rethink about this last one I posted...

[Doc.AElstein]

Transpose Alternative For VBA Arrays

Hi,
. I think This fits in as an appropriate Follow- up here.
. Thanks partly to this Thread I am getting fairly confident with handling simple VBA Arrays of different Dimensions. I have even managed to answer therefore a few Threads using extensively VBA Array as alternatives to other given methods,..
.
. One thing I frequently need to do is use the .Transpose Method. I am continually hearing that The .Transpose Method can be limited / inefficient, and that despite using looping a “simple routine” is better. I have not been able yet to google specific Threads on this Theme, comparing the two methods and giving example codes..
. So some Questions therefore
. 1 ) Can anyone point me in the direction of any previous work on this[/B]..Specifically comparing the methods with sample codes..

. 2) The “simple routine” I mentioned, is simple enough even for me to write ( so I did! ) I give it below.
. A simple routine in a normal Sub. is given and a Pubic Function based on that simple routine. - I was wondering if anyone can comment on this, suggest improvements, give alternative codes or Functions. Etc.…. (…. I was thinking, for example somehow re dimensioning something or everything to string: - I could change the dimensioning of the outArr() as String when using the Code in Normal Sub., but the Function would then not work, - The Function requires the argument as variant. - I defined it so as I could not come up with any other combination of dimensioning to get the Function to work !.... )

Thanks
Alan

Here my simple routine:
. In the following example I basically transpose a simple Range put into an Array using a simple routine in a normal Sub. Then I use a Pubic Function based in that simple routine to re – transpose the Array and then the spreadsheet range back to its original form..
. So initially, and finally, my spreadsheet looks like this:

Using Excel 2007

-	A	B	C	D
1	HAone	Htwo	Hthree	Hfour
2	Atwo	B2	C2	D2
3	Athree	B3	C3	D3
4	Afour	B4	C4	D4
5	Afive	B5	C5	D5
6	Asix	B6	C6	D6

Transpose

…
Along the way it looks like this

Using Excel 2007

-	A	B	C	D	E	F
1	HAone	Atwo	Athree	Afour	Afive	Asix
2	Htwo	B2	B3	B4	B5	B6
3	Hthree	C2	C3	C4	C5	C6
4	Hfour	D2	D3	D4	D5	D6

Transpose

………………………….

Here the normal Sub. code:

Please Login or Register  to view this content.

And here the Function Used:

Please Login or Register  to view this content.

[Kyle123]

Your code's fine there's only so many ways you can transpose something and yours is efficient. Why would you want to change it to a string array? In its current state it can be used for mixed types, a string array couldn't

[Doc.AElstein]

Your code's fine there's only so many ways you can transpose something, nd yours is efficient. Why would you want to change it to a string array? In its current state it can be used for mixed types, a string array couldn't

. Thanks for the reply Kyle,
. The strings thing was just a small thought, I just thought if I had only strings then somehow it could be quicker, along the whole .Strings / $ etc, thing we discussed here
http://www.excelforum.com/excel-new-...-arrays-5.html
. But mainly as it was a very fundamental point / requirement I thought there may have been more to it, or that it had been considered often enough before. I was surprised not too Google anything.
. But if you think my routine, albeit very simple, is efficient than you have partly answered the question
. Thanks again
Alan


P.s. one quick question.. In VBA do you think the full code in a Sub, ( Line 60 to line 110 )or the Fuction ( Line 160 - Line 170 along with the Pubic Function ) approach would be the most efficient. Of course I will check / experiment , but maybe from your "in depth " knowledge you can think of a basic "way VBA is working idea" to make one more efficient than the other

[Kyle123]

I don't imagine there'd be much (if anything) difference. It is good practice to break code into smaller logical chunks however, it makes it easier to read, more re-usable and crucially easier to test - you should read up on "unit testing", I suspect that it will appeal to you.

In general, you should aim to write clear, legible and easy to follow code. Optimize later and then only if there's an issue, premature micro-optimisation is a colossal time waster

[Doc.AElstein]

Hi again Kyle,

I don't imagine there'd be much (if anything) difference. It is good practice to break code into smaller logical chunks however, it makes it easier to read, more re-usable and crucially easier to test - you should read up on "unit testing", I suspect that it will appeal to you.

In general, you should aim to write clear, legible and easy to follow code. Optimize later and then only if there's an issue, premature micro-optimisation is a colossal time waster

. Thanks a lot for coming back. Very wise advice. Which often saves me a lot of time.
.
. I have just hit another very relevant follow up problem .....
.... Possibly it may be a bit difficult to answer for you as you are not familiar with some recent Threads I answered such as these:
http://www.excelforum.com/showthread...53#post4093653
http://www.excelforum.com/showthread...t=#post4088055
http://www.mrexcel.com/forum/excel-q...l?#post4174643

. I will Do my best to simplify the question…..
….. Through apo and snb_ I have learnt an interesting new way to answer those sort of threads where some initial sheet with data needs to be sorted to new sheets based on various criteria. Tyically in such Threads, only specific rows and (or) columns from the Initial data sheet are to be given to the new sheets..
. . The new step I learned is the following alternative to get the final Output Array..

Please Login or Register  to view this content.

. rws() is a one dimensional (What I call „psuedo Horizontal“ Array) filled with the required „row” indicies of required output lines, and Clms() is a 1 column 2- dimensional Array filled with the indicies of the required “columns” ( The actual indicies required are obtaind and those Array created in various ways previously in the code.. )
.
. I have noticed by chance ( due to my somewhat “overdoing it “ with too many steps, or commands !! ) that this errors

Please Login or Register  to view this content.

.. the error being incompatible types at rws().

. I have been googling and experimenting some time to find why this is…. Possibly you may see immediately the reason, based on the difference between .Index and .WorksheetFunction.Index
??

Thanks,
Alan

[Kyle123]

Some functions do vary when called through Application.WorksheetFunction.Function vs Application.Function, which other ones are I can't quite remember.

Remember that whilst using formulas on arrays is possible, it usually much slower than simply looping through - on small arrays this is unlikely to matter a great deal, but it can have an impact on larger ones. Though don't forget the premature optimisation!

As an aside, the method you discuss is quite nicely written up here by DonkeyOte: http://www.excelforum.com/tips-and-t...2d-arrays.html

[Doc.AElstein]

.... Though don't forget the premature optimization!.....

.. your 100% right - I do have a problem / addiction, which is not always healthy. I may seek medical advice or just have to "Cold Turkey " me off my excel affliction soon - RL may force that…...)

…but while I am still for now “at it / inflicted…”
Some follow up Questions / observations..

…….Remember that whilst using formulas on arrays is possible, it usually much slower than simply looping through - on small arrays this is unlikely to matter a great deal, but it can have an impact on larger ones.!...... .

.. That surprises me a bit….. Often, in codes , as in earlier Posts in this Thread ( Post #7 ) I use the "Slicing of an Array idea", to get at 1 column.
.. I got that through Rick Rothstein and here
https://usefulgyaan.wordpress.com/20...ication-index/
I had thought I had read somewhere else as well, (maybe deep down in an Old Rick Rothstein Thread ) that speed advantages were reported over looping, at least in this case...(. Unfortunately in the amazing amount of Threads In this world, am unable to relocate it again just now..…)
So question 1) .. Any general comments on that, if you are familiar with this Method ?
………………………………….________________

…………………………………….

…… As an aside, the method you discuss is quite nicely written up here by DonkeyOte: http://www.excelforum.com/tips-and-t...2d-arrays.html.....

.. I note he goes straight from looking at Things in the spreadsheet with
=INDEX(_________)
To VBA codes with
Application.Index

Missing out the
Application.WorksheetFunction.Index(__________)

. I am unclear as to whether WorksheetFunction.Index is equivalent to =INDEX( in a spreadsheet. Or to make it even more confusing.. Are either of the following or both equivalent to
In VBA =Evaluate(“INDEX(____________”)

. I would like to experiment that one ,
.But it does get endless, and I do need to be careful about going / coming prematurely at these things...
. …So question 2): Have you ( or anyone else looking in ) got any input to “save” me on these last few points, that is to say
. 2 a ) are
=INDEX(_________)’…..Spreadsheet
=Evaluate(“INDEX(____________”)’….VBA Code
Application.WorksheetFunction.Index(__________)’…………VBA Code
… equivalent,
and again in case anyone else catching this Post can tell me if that is correct, or partly,
. 2 b )what is the difference in “working” / syntax between those and
Application.Index(__________)’…………VBA Code

Thanks again for the great Profi Input giving benefit of your experiences to help “save me” from my affliction!!
Alan

[Doc.AElstein]

… just a small point along the way ( or rather mistake in my thinking ) which I just noticed myself..so thought I should post it…

Transpose Alternative For VBA Arrays

.......

. 2) The “simple routine” I mentioned, is simple enough even for me to write ( so I did! ) I give it below.
. A simple routine in a normal Sub. is given and a Pubic Function based on that simple routine. - I was wondering if anyone can comment on this, suggest improvements, give alternative codes or Functions. Etc.…. (….

!!!!!! - if I am really trying to mimic the .Transpose then I should have remembered what I detailed myself about the behavior of the .Transpose applied to the case of a 2 Dimensional 1 Column Array. As shown in the codes around Post # 1 and Post # 7, the .Transpose in that case had the peculiarity of returning my “Pseudo Horizontal” 1 Dimensional Array. Hence my simple Fuction will not truly reproduce the .Transpose in that case.. – it would simply return a 2 Dimensional 1 row Array

So How about 2 codes .. An actual Transpose Fuction and a “Dot”Transpose Fuction

Code: Fuction Transpose

Please Login or Register  to view this content.

Code: Fuction “Dot”Transpose

Please Login or Register  to view this content.