Functions to Re Dim Preserve First dimension in 2 D Array and Transpose ByValue

[Doc.AElstein]

Hi,
I have done variations of all the above in answering Threads quite a few times, so I thought a Thread on it here would not go a miss.
Many of us will know that VBA only allows you to change the last dimension ( “column”) in a 2 D Array whilst Preserving the current contents.
It may also be known that a simple workaround is to transpose the Array, Re Dim Preserve on the last dimension of the transposed Array, then transpose back.
Further, The Worksheet Function Transpose has some limitation such as size restrictions and a few other weird quirks
https://newtonexcelbach.wordpress.co...2013-and-2016/
http://excelmatters.com/2016/03/08/t...2013-and-2016/
_...and simple looping is often quicker anyway, so that is convenient to do here as well, Function(s) to do that which are then used in place of the Worksheet Function Transpose.
One further observation: It is often said that an Array cannot be passed ByValue. Only if one is pedantic defining an Array specifically in such a form arr() is that really the case. If we consider an Array as a "Field of elements filled with Variables", then this allows us to pass "it" ByValue. So the Functions are done in such a way as to allow the passing ByValue ( or ByReferrence )
http://www.mrexcel.com/forum/excel-q...l-byref-3.html
http://www.mrexcel.com/forum/excel-q...t-error-2.html
http://www.mrexcel.com/forum/excel-q...l-byref-2.html ( some minor typos )

So initially,
_1 ) just for completeness and comparison, a simple Function which does use the Worksheets Function Transpose. It assumes that we are dealing with a 2 Dimensional Array, both for the input Array and output Re Dimensioned Array. As in all cases I am assuming you want to enlarge the Array, but I think they would all work to “chop off a row”, although there are more efficient ways to do that, for example
http://www.excelfox.com/forum/f22/de...nt-array-2083/

Function and a calling Code to test Worksheets Function .Transpose way

Please Login or Register  to view this content.

_................................................


Then two basic pairs of Functions. The first function is the Function to Re Dim Preserve the first Dimension, or “row”. The second function is the VBA Looping alternative to the Worksheets Function transpose which the First Function Calls twice.

_2a) and 2 b). : The first pair are simple assuming that we are dealing with a 2 Dimensional Array, both for the input Array and output Re Dimensioned Array

_...................



_3)
_3b) In the second pair, the Second Function is intended to mimic exactly the Worksheet Function transpose, which will accept a 1 D Array, transposing it to a 1 Column 2 D Array, as well as in reverse returning a 1 D Array when given 1 column 2 D array.
_3a) Also the first function mimics partly, the VBA Re Dim Preserve, in as far as that it will accept a 1 D Array, although as with both Re Dim Preserve Functions given here they are intended to add a “column”

_.................................

I give the codes simplified here, with a demo calling code, , then in the next Posts a bit more detailed with lots of explaining ‘green comments for anyone interested

_2 ) ' Demo Code to call Functions. The First Function calls the second twice as necerssary to do the Transposing

Please Login or Register  to view this content.

_..........................

_3) 'The following set of Codes , to complete the picture, mimic the .Transpose. But again with simple looping to overcome the size limitations.

Please Login or Register  to view this content.

[Doc.AElstein]

http://www.excelforum.com/showthread...t=#post4378087
http://www.excelforum.com/showthread...t=#post4378086


Full Codes with explaining ‘Comments
Codes _ 2 ) ... assuming that we are dealing with a 2 Dimensional Array, both for the input Array and output Re Dimensioned Array

_2 ) ' Demo Code to call Functions. The First Function calls the second twice as necerssary to do the Transposing

Please Login or Register  to view this content.

[Doc.AElstein]

http://www.excelforum.com/showthread...t=#post4378088
http://www.excelforum.com/showthread...t=#post4378086



For Final “mimic .Transpose” Codes. Here the Demo Calling code and the First ( Re Dim Preserve ) code


_3) 'The following set of Codes , to complete the picture, the Second Function is intended to mimic exactly the Worksheet Function.Transpose. But again with simple looping to overcome the size limitations.
_3)
_3a) Also the first function mimics partly, the VBA Re Dim Preserve, in as far as that it will accept a 1 D Array, although as with both Re Dim Preserve Functions given here they are intended to add a “column”

Please Login or Register  to view this content.

[Doc.AElstein]

http://www.excelforum.com/showthread...t=#post4378089
http://www.excelforum.com/showthread...t=#post4378086



For Final “mimic .Transpose” Codes. Here the Second Function ( Transose )

_3b) In the second pair, the Second Function is intended to mimic exactly the Worksheet Function transpose, which will accept a 1 D Array, transposing it to a 1 Column 2 D Array, as well as in reverse returning a 1 D Array when given 1 column 2 D array.

Please Login or Register  to view this content.