+ Reply to Thread
Results 1 to 20 of 20

Trouble writing huge array into worksheet range

  1. #1
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Trouble writing huge array into worksheet range

    I admit I'm still learning the mechanics of building large-set arrays in memory, then writing the resulting data out to the worksheet all at once at the end.

    I've written a macro that builds a one-dimensional array in memory
    Please Login or Register  to view this content.
    Over simplified, of course. End result, MyArr in memory has 68722 rows and each row is unique.

    Question - how do I write MyArr out to G2:G68723 in one swoop?

    I've tried:
    Please Login or Register  to view this content.
    ..but this seems to just write out the FIRST value from MyArr into all those rows. Instead the 68722 unique strings I've built, I get MyArr(1) over and over. So I think I'm close, but no cigar.

    Extra Credit - the REAL goal is to split this array into 8 columns, parsing by the commas. I've tried:
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    Thanks in advance.
    Last edited by JBeaucaire; 09-23-2015 at 11:40 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Trouble writing huge array into worksheet range

    Read the link below

    http://www.ozgrid.com/forum/showthre...=194329&page=2

    I'd do like
    Please Login or Register  to view this content.
    then
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    As jindon notes, TextToColumns is probably the best option, though you could use something like:
    Please Login or Register  to view this content.

    Some other pointers since you're working with large arrays:
    • Don't use ReDim Preserve, it's inefficient. It makes an empty copy of the array and then copies all the data into it each time it's called. It's best if you know the maximum upper bound beforehand, if not you might find mileage in collections/dictionaries (before manually re-implementing Redim preserve)
    • Looped worksheet calls are bes avoided
    • Application.Transpose is slow and will fail over so many items (I forget how many) and with long strings - it's usually quicker to transpose the array manually with a loop

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trouble writing huge array into worksheet range

    Yes, yes, yes! Awesome, thanks! Will read through that linked info! Woo, this is so fast with that much data.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trouble writing huge array into worksheet range

    I'd initially built the array as MyArr(1 to NR, 1 to 8) and was actually filling the separate values into each field. When I couldn't get that beautiful array to write back to the sheet (forget 8 columns, not writing at all!) I went back to something I'd used successfully in the past, a single dimension with delimited data.

    Not sure I'll go back and rewrite this again, it's pretty fast.

    I went ahead and initially dim the array with 100,000 rows, seems to be good, then added a tweak to only redim if we max out the initial array.

    Thanks guys.
    Last edited by JBeaucaire; 09-23-2015 at 12:58 PM.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    Glad I could belp

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

    Re: Trouble writing huge array into worksheet range

    Quote Originally Posted by Kyle123 View Post
    Application.Transpose is slow and will fail over so many items (I forget how many
    All the worksheet functions fail if the array is over 65536 rows (ie the size of worksheets pre 2007)
    Remember what the dormouse said
    Feed your head

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    I knew the limit was the old number of rows, but I can never remember what the 65k number is

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Trouble writing huge array into worksheet range

    Quote Originally Posted by Kyle123 View Post
    I knew the limit was the old number of rows, but I can never remember what the 65k number is
    If you check the link I posted, you will see that is 2^16.

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    Ta, I understand where it comes from - but I'm never bothered enough to work it out

  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: Trouble writing huge array into worksheet range

    Hi JBeaucaire,
    _ this thread caught my interest a bit.....Sorry!!

    Quote Originally Posted by JBeaucaire View Post
    I admit I'm still learning the mechanics of building large-set arrays in memory, then writing the resulting data out to the worksheet all at once at the end.....
    I've tried:
    Please Login or Register  to view this content.
    .......
    _ 1 ) I use that VBA “0ne liner Range assigner” .Resize().Value = arrOut() continuously also for very massive Arrays. It has never failed me. I love it. It is very fast. Like Kyle said a lot of other stuff especially WorksheetFuctions are slow. ( I have been continually surprised how quick things go if you do as much internally with good old fashion logic, occasionally simple loops. ) ( ReDim preserve did not seems not too slow thought, by me. Nor did filling your 1 D Array. )

    _ Some observations / remarks on that “0ne liner Range assigner”:
    _ Rem 1 ) I played a bit and did a version of your code here:
    http://www.excelforum.com/developmen...ml#post4198803
    https://app.box.com/s/2q5z02mnzyglslz3q7r9965lx4l7jmgw ( Code in Worksheet “JBeaucaire” Module )

    _ I guess you realised that initially you built a 1 D “pseudo horizontal going to the right” Type Array., and you actually needed a 1 column 2 D Array as jindon showed.

    _ If you have time or interest to look at that code ,...
    Quote Originally Posted by JBeaucaire View Post
    .....but this seems to just write out the FIRST value from MyArr into all those rows.....
    Line 140 I learnt something very interesting here: I would have expected you to get just the first value in cell G2 and all others empty as is the case if you do the same “0ne liner Range assigner” using a 2 D 1 row Array.
    So for the 1 D Array the “0ne liner Range assigner” pastes a bit differently which is interesting . Weird

    Lines 160 and 170 Here you have the usual 2 D Array case and you would have been trying to paste out to a long column staring at G2 with a long “row” starting at G2, so you only catch the “top left corner”, as it were. I expected that.
    Clearly VBA thinks a bit differently with the 1 D Array case in the “vertical” case

    _ Rem 2)
    Line 200 Here In the “horizontal” case VBA seems to think “normally” with the 1 D Array. – If you do the same again with your 1 D Array but resizing properly now to “suit” , “along” the row and pasting out using the “0ne liner Range assigner” then as expected you get a row of all your values. As you often find, ( and the reason why I call it 1 D “pseudo” Horizontal” ) this mirrors
    Line 230 where I do exactly the same using a 2 D 1 “row” Array with the “0ne liner Range assigner”
    ( For the above two lines I had to limit ( reduce ) the demo outputted cells a bit because of the Excel Column limit which is a bit under your wanted total number of cells )

    _ Rem 3)
    Lines 270 – 400 then do what you originally wanted with the “0ne liner Range assigner” working on a 1 “column” 2 D Array, as jindon indicated . ( It puts what you want in column H, starting at H2) ( I used a Fuction of mine instead of .WorksheetFunction.Transpose as you had > 65536 rows, so you hit the limit Rory mentioned, post # 7 )


    _ '======================================


    _ Rem 4) Actually my main reason for popping by here in this thread was that although I did not quite understand what you actually wanted to do
    Quote Originally Posted by JBeaucaire View Post
    .....Extra Credit - the REAL goal is to split this array into 8 columns

    I'd initially built the array as MyArr(1 to NR, 1 to 8) and was actually filling the separate values into each field. When I couldn't get that beautiful array to write back to the sheet (forget 8 columns, not writing at all!).......
    I thought nevertheless a variation of the following might at least be worth a quick thought: Code lines from 460 are suing one of the few VBA Functions I still use , the "...use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. ...” ( Note NOT Application.WorksheetFunction.Index, which actually does not work).....
    http://www.excelforum.com/excel-new-...ba-arrays.html

    _ As I did not quite catch exactly what you may have been doing I may have gone off at a bit of a tangent, but maybe if you have time to run that code ( in F8 preferably ) there could be some idea there?! It is another quick way, a “one Liner” to pick out your columns, any combination and any order there of....)
    _ Anyways.. I found it good anyway to check some of the size / speed issues discussed here and that weird characteristic of the ID Array pasting out all cells initially with “ MyArr(1) over and over.“ Made me think again about exactly how VBA treats 1D Arrays…

    Alan
    P.s. One very last point. Or Question or request.
    This "...use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. ...” is a thing I like a lot. If you look at line 460 you will see that I had to limit NR to a maximum lNR of 65535
    This was because otherwise my “magic" line 660 failed. Do you think this is the Limit Rory mentioned ( Post # 7 ) ?? I was hoping my Application.Index might not have that limit as it works differently to Application.WorksheetFunction.Index ??

    Thanks
    Alan
    '_- 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 )

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    This was because otherwise my “magic" line 660 failed. Do you think this is the Limit Rory mentioned ( Post # 7 ) ?? I was hoping my Application.Index might not have that limit as it works differently to Application.WorksheetFunction.Index ??
    Yes, it's still a worksheet function, though as you've discovered some do behave slightly differently when called by Appplication.Function vs Application.WorksheetFunction.Function (though they shouldn't really in theory)

  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: Trouble writing huge array into worksheet range

    Hi Kyle,
    Quote Originally Posted by Kyle123 View Post
    Yes, it's still a worksheet function, though as you've discovered some do behave slightly differently when called by Appplication.Function vs Application.WorksheetFunction.Function (though they shouldn't really in theory)
    , OK Thanks..
    _ Sounds really weird though,
    _ 1 ) I am using it in a pretty impressive way to do some awesome stuff. That that can somehow be dependent on how it is “called” is a weird idea to grasp. I think if i remember on those times I used....
    Application.WorksheetFunction.Index
    Instead of
    Application.Index
    .....The error was something about there being type incompatibility with the Array element Argument. I guess some obscure reason on the different “path” it comes through might explain that. I seem to remember that Arrays are sometimes the only variables that are not allowed in some “places”. Maybe in the “call” route .WorksheetFunction the thing stopped off somewhere along the way for a cup of coffee where the storage of Arrays were not allowed so they got wiped out. – Just another mental Alan Theory that is probably right?..... sort of.....

    _ 2) A lot of experienced people have often suggested the use of a .Application instead of a .Application.WorksheetFunction as a definite “Different” alternative.. The Application.Match is a good example that springs to mind. There it avoided error handling compared with the .Application.WorksheetFunction.Match which errored if no match position was found. ( The Application.Match gives / returns an error rather than actually causing an error situation to be raised up. ) Hmm.. Coming to come, to think in that way... An error is a weird thing, like an animal or pet that over the “old” Worksheets route it is not allowed to have at the “stop over point coffee shop or whatever.. “

    _ .. I wonder if we have it there then: Calling over the old Worksheets route currently cause some things to error as awkward things like Arrays, Errors, Stinky Dogs etc. etc. Are not allowed at this “stop over” point.
    _ .. Maybe that will change sometimes suddenly without anyone noticing by some update, when the “Stop over Place” is rebuilt with an Array Holding facility, Error Area, A kennel for your Dog and his Nuts etc. etc. – Maybe that is another sensible reason not to use an expected error with error handling as part of a code condition.. – Maybe suddenly when things stop erroring a few people will be very upset that there codes no longer work......after all errors are sort of bad.. maybe them not happening would be thought of as good improvement for an update...

    Alan

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Trouble writing huge array into worksheet range

    As you've touched upon, the difference is largely in the error handling, have a read here http://dailydoseofexcel.com/archives...nction-method/ though I'll stop at that since this is veering way off topic

  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: Trouble writing huge array into worksheet range

    Hi
    Quote Originally Posted by Kyle123 View Post
    As you've touched upon, the difference is largely in the error handling, have a read here ..... though I'll stop at that since this is veering way off topic
    Thanks for that Link.That clears it part up. Still a bit of a mystery the .Index stuff.
    But for another Day, or Thread , or Wotever
    Alan

  16. #16
    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: Trouble writing huge array into worksheet range

    Hi Alan ( and kyle ) ....
    Quote Originally Posted by Doc.AElstein View Post
    ..........
    P.s. One very last point. Or Question or request.
    This "...use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. ...” is a thing I like a lot. If you look at line 460 you will see that I had to limit NR to a maximum lNR of 65535
    This was because otherwise my “magic" line 660 failed. Do you think this is the Limit Rory mentioned ( Post # 7 ) ?? I was hoping my Application.Index might not have that limit as it works differently to Application.WorksheetFunction.Index ??...
    ... you may be interested in a small interesting thing I learned from an additional solution given in this Thread...
    http://www.mrexcel.com/forum/excel-q...ml#post4335072

    In a code alternative to my often used “Magic line” ....
    "...use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. ...”
    arrOut() = Application.Index(arrIn(), rws(), clms())
    http://www.excelforum.com/excel-new-...ba-arrays.html

    a similar code line was used. However the first argument ( “Grid” ) of the Index Fuction was a Range rather than an Array.
    AND that Range was actually the Worksheet property Cells, thereby including the entire “Worksheet Cell values”

    arrOut() = Application.Index(Cells, rws(), clms())

    Initially I thought “Ahh.... he is using XL2003 or lower” - and so only has the 65536 rows only ..
    But I experimented, and even in XL 2007 and above the above line works! One is still limited to the Output and Second ( “row” ) argument having 65535 maximum, but the First arguments can be an entire Spreadsheet!!!

    AND importantly it works very fast, ( - as often appears to be the case, with Accessing values from a spreadsheet,- not as slow as other interactions with the Spreadsheet. (.... I guess there is a parallel with how the magic line works here and the typical VBA Allowed “one liner” Capture of the form
    Arr()=rng.Value
    ...
    .)-
    )

    AND I also checked and one can access any part of the Spreadsheet this way.

    AND it overcomes the problem I sometimes face when my arrIn() is very big – I certainly cannot achieve
    arrIn()=Cells.Value

    Bottom line is that this appears to be one case when an otherwise predominantly VBA Array type code could be enhanced / improved with this one Spreadsheet interaction.

    I did a code to step through illustrating the above points
    _ - 1) initially some arbitrary values are put in all rows for a couple of columns..

    Using Excel 2007
    Row\Col
    A
    B
    1
    rwA1 rwB1
    2
    rwA2 rwB2
    3
    rwA3 rwB3
    4
    rwA4 rwB4
    5
    rwA5 rwB5
    6
    rwA6 rwB6
    7
    rwA7 rwB7
    ShtJBeaucaire


    _ - 2a) Then the full Array approaches are done with the limits discussed in the ' Comments

    _ - 2b) Various code lines demonstrate the improved size of accessible Range ( “Grid” ) using the Cells as First Argument (“Grid” ) in the Index Function

    Code Here:
    http://www.excelforum.com/developmen...ml#post4238675

    Hope this helps Alan with , your insistence on using one ridiculously large Spreadsheets!!!
    http://www.mrexcel.com/forum/microso...estion%94.html

    Alan

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

    Re: Trouble writing huge array into worksheet range

    If you're using a full sheet of cells, I think you're using the wrong application.

    As for the rest, I think we already covered the array limits, and it's not altogether surprising that worksheet functions work well with ranges.

  18. #18
    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: Trouble writing huge array into worksheet range

    Hi Rory
    Quote Originally Posted by romperstomper View Post
    If you're using a full sheet of cells, I think you're using the wrong application....
    _ . I am not using them all, but I take your point

    _ I was just surprised that the first argument could be an entire Sheet, whereas if it was an Array as first argument then just the usual row limit. – Gives an extra possibility when i use that Index function line, that’s all.
    Alan

  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

    Thoughts about Thinking how VBA is Thinking

    Thoughts about Thinking how VBA is Thinking

    This Thread is solved, pretty well, but there is one small point unanswered, i think I have got a good understanding of that now, so a quick follow up I thought would not go a miss to share my thoughts..
    _...
    Regarding:
    Quote Originally Posted by JBeaucaire View Post
    ......
    ..but this seems to just write out the FIRST value from MyArr into all those rows. .......
    .......
    140 ws.Range("G2").Resize(UBound(MyArr1D())).Value = MyArr1D() 'Strangely puts first value in all column cells?
    150 ws.Columns("G").ClearContents
    160 ws.Range("G2").
    Resize(UBound(MyArr2D1R(), 1), 1).Value = MyArr2D1R() 'As expected Assigning a Long "row" to a long "column" only catches ( Reveals in spreadsheet ) the top left corner. Cells going “down” remain empty

    170 ws.Columns("G").ClearContents: ws.Range
    ("G2").Resize(UBound(MyArr2D1R(), 1)).Value = MyArr2D1R() 'As expected Assigning a Long "row" to a long "column" only catches ( Reveals in spreadsheet ) the top left corner. Cells going “down” remain empty
    180
    190 If UBound(MyArr1D()) > ws.Columns.Count - ws.Range("H1").Column Then ReDim Preserve MyArr1D(1 To (ws.Columns.Count - ws.Range("H1").Column)) 'Had to limit the demo because of Excel Column limit
    200 ws.Range("H1").Resize(1, UBound(MyArr1D())).Value = MyArr1D() 'Resizing Range properly to "suit" , "along" the row and pasting out using the "0ne liner Range assigner" then as expected you get a row of all your values
    210 ws.Rows(1).ClearContents
    220 If UBound(MyArr2D1R(), 2) > ws.Columns.Count - ws.Range("H1").Column Then ReDim Preserve MyArr2D1R(1 To 1, 1 To (ws.Columns.Count - ws.Range("H1").Column)) 'Had to limit the demo because of Excel Column limit
    230 ws.Range("H1").
    Resize(UBound(MyArr2D1R(), 1), UBound(MyArr2D1R(), 2)).Value = MyArr2D1R() 'Using a 2 D 1 "row" Array with the "0ne liner Range assigner". The Top Left Cell of where it should go has its Range Size ( number of cells ) and Orientation Correctly resized to suit exactly the size and orientation of the many element Array
    240 ws.Range("G2").Resize(UBound(MyArr2D1R(), 1), 1).Value = MyArr2D1R(): ws.Columns("G").Clear: ws.Range("G2").Resize(UBound(MyArr2D1R(), 1)).Value = MyArr2D1R()
    250 ws.Range("G2").Resize(UBound(MyArr2D1R(), 1)).Value = MyArr2D1R()
    260


    _..............................
    First what are ( were ) we talking about

    Running through my demo code
    http://www.excelforum.com/developmen...ml#post4198803
    and explaining with emphasis on this outstanding point.

    _1 Minor Point First.
    VBA allows conventionally you ( maybe with some internal Function ) to paste out an "un orientated" 1D Array into a spreadsheet, and chooses convenientlly to give you that in the "row" orientation:

    _2) The main curiosity outstanding was that when pasting out such a 1 D Array incorrectly into a column , then rather than a just getting the Top ( Left ) Array value, - I obtain unexpectedly the First value from the Array down the entire column.


    Working backwards a bit may help explain both the question and answer
    ...
    Line 230 gets it right. It pastes a 2 D , 1 row , many column Array , ( MyArr2D1R() ) out "accross" starting at Cell H1

    Using Excel 2007
    Row\Col
    H
    I
    J
    K
    1
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3 1.4,2.4,3.4,4.4,5.4,6.4,7.4,8.4
    2
    3
    4
    JBeaucaire
    _...................................

    Line 160 "gets it wrong". It attempts to paste the exactly the same Array "down" a long column starting at cell G2. So as we possibly expect, we only see the first value, the Top Left as that Top left Cell is the same if we attempt to paste an Array "across" or "down" starting at that Top left Cell.

    Row\Col
    F
    G
    H
    1
    2
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    3
    4
    5


    _.....

    Now the outstanding oddity...
    Line 140
    I repeat the last bit, but with a 1 D Array containing identical values to the 2 D , 1 row , many column Array used in the last two demos.

    I may expect then to get the same results as with Line 160
    I don't
    I get this:
    Row\Col
    F
    G
    H
    1
    2
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    3
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    4
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    5
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    6
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    7
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1
    8
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1


    So The explanation
    _. Modify slightly Line 140 so as to attempt to paste out a square area starting at the same Top Left Position, say for example
    135 ws.range("G2:I8").Value = MyArr1D()
    Re run...
    You do get this now:
    Row\Col
    F
    G
    H
    I
    J
    1
    2
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    3
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    4
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    5
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    6
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    7
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    8
    1.1,2.1,3.1,4.1,5.1,6.1,7.1,8.1 1.2,2.2,3.2,4.2,5.2,6.2,7.2,8.2 1.3,2.3,3.3,4.3,5.3,6.3,7.3,8.3
    9
    10
    11
    12
    13
    14

    _................................
    My thoughts on thinking how VBA is thinking..
    VBA does not hold or "see" a 1 D Array as a "row" of numbers, as we might "see" it. It holds them as set of vertical Grid lines. Consider it as holding a Brush. Each Brush Strand represents a “column” which extends down the whole sheet. ( The concept is a bit tricky.. this “column” is a transpose of the actual spreadsheet column.. Up there we see extended rows. Think of it another way – a single row going up and down very quickly as it has not been “tied down” yet. Here in the assignment to a Range "Area" we tie up what we see. By defining it as 2 D 1 Row Array would also give it a “place” and orientation or “living in a box” even if that is still not yet tied to a Range "Area". )
    VBA gets at Cells that we "see”, ( or looking at in another way, it offsets from a start point, ) by typically holding two brushes and where the strands intercept is the Cell reference.
    VBA therefore tends to work very quickly with I Dimensions. As a Parallel: we would look along and up the columns of a grid on a map to get to a coordinate position. VBA just looks up the intercept reference already there: VBA is already there and just takes the correct referenced ( intercepted ) Cell.

    By default, in this process, when a column is not specified it “sees” all “columns” with each row. This transpose of the actual column will give intercepts at each actual row, resulting in this “extending” of the row numbers downwards..

    This can help explain some other interesting things, which are often reported but rarely explained , at least in terms of how they work. In VBA 0 is often the indication for "all". I would suggest it is more likely rather the default ( as it often is ) as this is the start point. It actually does nothing where it might otherwise. ( I personally would not necessarily miss out when i use the implicit default BTW:, but I would arrange a code to work with it, as it may well be the most efficient. If possible I would still write it in both to be on the safe side and remind me what is happening )

    _.......................
    _.. continued in next post...( due to post size limitations )
    Last edited by Doc.AElstein; 05-07-2016 at 05:48 AM.

  20. #20
    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: Thoughts about Thinking how VBA is Thinking

    _....continuing.. from last post

    Consider the "splitting concept" of an Array. Such a common code line is often seen as a quick demo:
    A code of this form
    Please Login or Register  to view this content.
    would work on this
    Row\Col
    A
    B
    C
    D
    E
    1
    A1 B1 C1 D1
    2
    A2 B2 C2 D2
    3
    A3 B3 C3 D3
    4
    A4 B4 C4 D4
    5


    returning this

    A2 B2 C2 D2


    Some, alternative similar code lines, (.... some explanations given here:
    http://www.excelforum.com/excel-new-...ba-arrays.html
    http://www.mrexcel.com/forum/excel-q...ml#post4375354
    _....)
    Please Login or Register  to view this content.
    will return respectively

    A2 B2 C2 D2

    and
    Using Excel 2007
    A2 B2 C2 D2
    A3 B3 C3 D3


    _Those codes are relying a lot on implicit defaults. If these are looked at in more detail, ( More precisely Declared in particular )
    we learn something very interesting which helps to give some support to the way VBA is "working / Thinking "

    Detailed code lines are given below, but in short, the very fist code line can be made to return us a Range rather than an Array of Values.
    Please Login or Register  to view this content.
    This is not possible with the second and third code lines.
    I would suggest this is because the "default" 0 is as the situation is at the outset when a VBA is presented an indicia with no specified second indicia to tie down a cell, which as discussed previously "presents" all columns. And it is able to present the entire Cell Objects. It is so to say, as well prepared as possible for what comes next!. Once we star added other indicies and conditions, things start getting "done" on the presented data causing speed and other limitations, and or initiating the default set operations, in this case converting to values


    With these thoughts in mind i have recently re-rewritten some long code of mine which predominantly used for speed considerations Arrays. I was able to in some instances inclide some workshhet interaction taking advantage of " thinking", as I was thinking VBA is "Thinking " to increase some codes speeds and efficiency considerable.

    I tried to summarise some of my thinking in this Thread Post just now:
    http://www.excelforum.com/showthread...t=#post4380613

    Alan
    _....

    Full Demo Code here:
    http://www.excelforum.com/showthread...t=#post4380604

    And File with all updated Demo Codes
    https://app.box.com/s/7wr4tqfjrefcumz1xyasrv1z3arxhr03
    Last edited by Doc.AElstein; 05-07-2016 at 05:56 AM.

+ 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] Writing elements of array to a range
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2015, 12:42 PM
  2. writing a range to an array then writing the array to a new range
    By prjames3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2014, 08:57 PM
  3. Writing an additional 'IF' into a huge 'IF' formula??
    By grholden in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-24-2014, 06:12 AM
  4. writing formula in range on each sheet via array
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2011, 12:59 PM
  5. Writing a range to an array...
    By Alex in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2005, 10:10 AM
  6. [SOLVED] Writing an array(2d) of user types to a worksheet
    By John Keith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2005, 08:30 PM
  7. [SOLVED] Writing values to worksheet from array
    By interstellar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-12-2005, 01:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1