Maximum Size of a VBA Array in Excel 2007

Topic

New Reply

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?

Reply | Quote

Replies

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/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel

Reply | Quote

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

Reply | Quote

This one may help.
Here’s a search with other possibilities.

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

cheers, Paul

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote