+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.
    Last edited by Doc.AElstein; 05-19-2016 at 08:23 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.
    Last edited by Doc.AElstein; 05-03-2016 at 06:57 PM.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.
    Last edited by Doc.AElstein; 05-03-2016 at 06:56 PM.

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    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.
    Last edited by Doc.AElstein; 05-03-2016 at 06:56 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1