+ Reply to Thread
Results 1 to 22 of 22

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

  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

    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:


    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
    Last edited by Doc.AElstein; 05-03-2015 at 04:22 PM.
    '_- 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 Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,752

    Re: VBA 1 – Dimensional Horizontal and Vertical Array conventions

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  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: VBA 1 – Dimensional Horizontal and Vertical Array conventions

    Quote Originally Posted by mehmetcik View Post
    …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.
    Last edited by Doc.AElstein; 05-03-2015 at 12:46 PM.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    (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.
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

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

  6. #6
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Quote Originally Posted by Kyle123 View Post
    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 ! )

  7. #7
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Hi Rory,


    Quote Originally Posted by romperstomper View Post
    ……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..

    ……………………….

    Quote Originally Posted by romperstomper View Post
    (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

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    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.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

    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.
    Last edited by Kyle123; 05-05-2015 at 10:47 AM.

  10. #10
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Hi Rory,

    Quote Originally Posted by romperstomper View Post
    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

    Quote Originally Posted by romperstomper View Post
    ….

    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

  11. #11
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Hi Kyle,

    Quote Originally Posted by Kyle123 View Post
    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

    Quote Originally Posted by Kyle123 View Post
    …….

    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.




    Quote Originally Posted by Kyle123 View Post
    …….

    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

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

    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.

  13. #13
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Quote Originally Posted by Kyle123 View Post
    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..

    Quote Originally Posted by Kyle123 View Post
    .........
    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!!)

  14. #14
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions

    - Having a rethink about this last one I posted...
    Last edited by Doc.AElstein; 05-07-2015 at 10:33 AM. Reason: Having a rethink

  15. #15
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions

    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.

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

    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

  17. #17
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Quote Originally Posted by Kyle123 View Post
    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
    Last edited by Doc.AElstein; 06-04-2015 at 01:24 PM.

  18. #18
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

    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

  19. #19
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Hi again Kyle,

    Quote Originally Posted by Kyle123 View Post
    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

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

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

    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

  21. #21
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    Quote Originally Posted by Kyle123 View Post
    .... 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..

    Quote Originally Posted by Kyle123 View Post
    …….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 ?
    ………………………………….________________

    …………………………………….
    Quote Originally Posted by Kyle123 View Post
    …… 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

  22. #22
    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: VBA 1 – Dimensional Horizontal and Vertical Array conventions ( ha 1, 2, 3, 4 )

    … just a small point along the way ( or rather mistake in my thinking ) which I just noticed myself..so thought I should post it…
    Quote Originally Posted by Doc.AElstein View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] 1D Array Naming Conventions
    By tom.hogan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2014, 10:51 AM
  2. [SOLVED] array formula for vertical and horizontal data
    By freud1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2013, 09:23 PM
  3. [SOLVED] vertical range into an one-dimensional array
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2013, 03:03 PM
  4. Creating a 2-dimensional array from a 1-dimensional list
    By guywithcamera in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2008, 06:34 PM
  5. Create One-Dimensional Array from Two-Dimensional Array
    By Stratuser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2005, 05:06 PM

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