Results 1 to 8 of 8
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm running into a problem that I think is caused by exceeding the maximum size of a VBA array in Excel 2007.
    The final size of the array is (201185 x 151)

    The procedure works fine with smaller data sets - but when I move to the larger data set, the final dump of the array doesn't occur.

    Suggestions? Confirmation of my theory?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Catharine Richardson - WebGenii View Post
    I'm running into a problem that I think is caused by exceeding the maximum size of a VBA array in Excel 2007.
    The final size of the array is (201185 x 151)

    The procedure works fine with smaller data sets - but when I move to the larger data set, the final dump of the array doesn't occur.

    Suggestions? Confirmation of my theory?
    Hi Catherine

    Does this article from the MSKB help http://msdn.microsoft.com/en-us/libr...sedLimitsExcel
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    This one may help.
    Here's a search with other possibilities.

    Maybe a dictionary would be better for a very large array?

    cheers, Paul

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by B. Bunter Esq. View Post
    Hi Catherine

    Does this article from the MSKB help http://msdn.microsof...asedLimitsExcel
    That refers to Array Formulas - but Paul's link led me to this . However, that article still says there should be an out of memory error.

    Oh, well I solved the problem a different way. Instead of holding the array of values taken from the spreadsheet. I created an array of addresses - referencing the values that I want to work with. This seems to scale up more robustly - tho' there is a slowdown when I assemble all the data together in one place.

    And did you know that something like Selection.Value = Range("ChromaArray").Value (to copy a block of cells into a selection) fails when I'm dealing with really big chunks of data. Again - no error msg, just a blank space at the end of the routine.

    Oh, the things I'm learning today
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by P T View Post
    Maybe a dictionary would be better for a very large array?
    I thought about it - I've never worked with dictionaries before. I did try a collection - that did actually throw an out-of-memory error before collapsing the routine.
    Then I realized the data was already sitting in the spreadsheet and re referencing it would work just fine. (Also in terms of this data -I don't need to manipulate it at this point, just collect it all together).
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Hi WebGenii,
    I think your are pushing the upper limits of Excel with the data you are dealing with. You have some 30 million cells, so to hit the out-of-memory range you would be averaging about 70 characters per cell. I have no idea how Excel stores data internally (don't think I want to either) - but that's not alot. I hit a similar problem a couple of years ago where I was trying to import about 500,000 records from a text file and then manipulate dates in the beast - there were all stored as text. Excel just kept quietly going away. I resorted to doing the work in Access where you don't have the memory issues with large recordsets.
    Wendell

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by WendellB View Post
    Hi WebGenii,
    I think your are pushing the upper limits of Excel with the data you are dealing with. You have some 30 million cells, so to hit the out-of-memory range you would be averaging about 70 characters per cell. I have no idea how Excel stores data internally (don't think I want to either) - but that's not alot. I hit a similar problem a couple of years ago where I was trying to import about 500,000 records from a text file and then manipulate dates in the beast - there were all stored as text. Excel just kept quietly going away. I resorted to doing the work in Access where you don't have the memory issues with large recordsets.
    When I originally wrote this routine we were using Excel 2003 and were working with smaller datasets.
    I have thought about Access from time to time, but the limitation of 255 fields in a table is there. Plus I'd still need to bring all the data back into Excel for the heat map.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Hmmmm - not sure why the 255 field limit in a table would be an issue. You currently have 151 columns in your data, so it should be possible to take it straight into an Access table. That may not be the best way however. You may be better off with a table that has a Row and Column index and a value. And there's nothing to say you can't build the display in Excel using automation. Just another way to peel the onion...
    Wendell

Posting Permissions

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