Maximum size of a VBA Array - Excel 2007

[WebGenii]

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?

[mbarron]

Everything I can find says that "The maximum number of elements in the array is limited only by available memory"

[WebGenii]

That's what my googling says as well. It also says there should be an out of memory error - which I don't get.
The procedure goes normally to the end - just no data.

BTW, my latest test 50250 x 151 works.

[steveh]

That's what my googling says as well. It also says there should be an out of memory error - which I don't get.
The procedure goes normally to the end - just no data.

BTW, my latest test 50250 x 151 works.

Hi Catherine

I posted an answer in the 'other place' (sorry admin, old habits die hard ), whether it is right or not or just confirmation of the above I don't know but this MSKB gives the various size limits in 2007 http://msdn.microsoft.com/en-us/library ... imitsExcel" onclick="window.open(this.href);return false;

[Goshute]

That's what my googling says as well. It also says there should be an out of memory error - which I don't get.
The procedure goes normally to the end - just no data.

BTW, my latest test 50250 x 151 works.

this MSKB gives the various size limits in 2007 http://msdn.microsoft.com/en-us/library ... imitsExcel" onclick="window.open(this.href);return false;

But Catherine, you are talking about an array in VB, not a spreadsheet Range, correct...?

[WebGenii]

Correct - a VB Array

[rory]

When you say 'no data' do you mean that there is no data in the array, or that when you try to output it somewhere, it doesn't appear? If the latter, how are you outputting it?

[WebGenii]

Code: Select all

'move over and fill with Array info
    Range(ActiveCell.Offset(0, 1)).Select
'    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(lngRowCount, lngColCount)).Value = SequenceArray
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(lngRowCount, lngColCount - 1)).Select
'set column widths
    ActiveWorkbook.Names.Add Name:="ChromaArray", RefersTo:=Selection
    Range("ChromaArray").Columns.ColumnWidth = 4
 'fill ChromaArray using addresses from SequenceArray to ReferenceData
    intCounter2 = 1
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(lngRowCount, 0)).Select
For intCounter2 = 1 To lngColCount
    Selection.Value = Range(SequenceArray(intCounter2, 1)).Value
    ActiveCell.Offset(0, 1).Select
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(lngRowCount, 0)).Select
    Erase CurrentArray
Next intCounter2

I know that the above works, because when I work with smaller amounts of data - the results are as expected.

[rory]

A couple more questions then:
1. What is in SequenceArray and CurrentArray?
2. What size are they?
3. Have you checked that they actually contain what you think they should?
4. Why are you erasing CurrentArray inside that loop when you don't appear to be doing anything with it, and one erase would work anyway?
Note also that the code would be more efficient without all the selecting... ;)

[WebGenii]

A little background http://stinet.dtic.mil/oai/oai?&verb=ge ... =ADA457115.
Big Picture; the researcher selects the number of bins, their alphabetical labels and the colors that will represent each bin/label.
The bins are created using the min and max value in each column.
The code loops through each column of data, assigning a bin label to each cell and placing a column of bin labels beside the data column.
After all the data columns have been labelled, each column of labels is copied to a new location in the spreadsheet.
This is done twice, since the researchers wanted a heat map and a map of labels side by side.
The heat map is created by looping through the cells, examining the label in each cell and applying the matching colour.

Now for your specific questions.
1. What is in SequenceArray and CurrentArray?
SequenceArray contains the addresses of the columns of labels from the original step (originally I was loading the bin labels into this array - but was running into my original problem - now just the addresses of the previously binned labels are in this array).
Current Array (oops an artifact of copy/pasting code that statement is not required at that point). But CurrentArray is used earlier in the procedure contain the values from each column as bins are being calculated.
2. What size are they?
Both SequenceArray and CurrentArray are 1 column by the total number of rows of data (201185 ) currently.
3. Have you checked that they actually contain what you think they should?
Yes, frequently and with paranoia. <grin>
4. Why are you erasing CurrentArray inside that loop when you don't appear to be doing anything with it, and one erase would work anyway?
You are completely right.
Note also that the code would be more efficient without all the selecting... ;)
Yes, I'm rewriting to avoid selecting. I don't know why I thought it would be so hard to write without selection.

[WebGenii]

A couple more questions then:
Note also that the code would be more efficient without all the selecting... ;)

For those who are interested - I just ran some test comparing the two version of my code
One version navigates around the spreadsheet using selection. One does not. In both, screen updating is turned off.

The final results:
Selected mode: 1240.234375
No selection mode: 1126.59375

So in the final analysis, nearly a 2 minute difference.

[TCJammer]

I believe your problem exists in your data typing. In the For loop you have For intCounter2 = 1 to lngColCount. Int's have a maximum value of 32767 while longs are much bigger. Change your Data type for inCounter2 to Long and see what happens.

[HansV]

Hi TCJammer,

Welcome to Eileen's Lounge!

[TCJammer]

Thank you, good to be here.

[WebGenii]

I believe your problem exists in your data typing. In the For loop you have For intCounter2 = 1 to lngColCount. Int's have a maximum value of 32767 while longs are much bigger. Change your Data type for inCounter2 to Long and see what happens.

I changed that yesterday after reading that all integers are changed to longs <grin>. Its good to have another set of eyes looking at things.

[agibsonsw]

Hello.
On this subject of large VBA arrays, is there a performance issue with manipulating a large array? If possible, should it be dealt with in separate 'chunks',
or is it purely down to avaialbe memory? Thanks, Andy.

[HansV]

Available memory is the crucial point. Keeping a large array in memory, if possible, is more efficient than working with several smaller arrays.

[WebGenii]

Hello.
On this subject of large VBA arrays, is there a performance issue with manipulating a large array? If possible, should it be dealt with in separate 'chunks',
or is it purely down to avaialbe memory? Thanks, Andy.

Hi Andy
This is what is puzzling to me. If the array was too large, and failed to load - that would be understandable. Instead it fails after looping 70 or 80 times. To me it seems that with each loop, more memory is consumed until there is not enough left.
Fortunately, yesterday we decided that placing the bin labels beside the data wasn't necessary. The previous results of procedure effectively quadrupled the size of the spreadsheet. Now it will only be tripled - surely that's much better.

[agibsonsw]

Hi.
I'm just guessing here, but I notice your code constantly references ActiveCell. It might be worth setting a range object to this outside the loops (Set rng = ActiveCell).
If ActiveCell occurs within the loops then I assume that VBA has to constantly 'check in' with Excel to verify the ActiveCell. Andy.
(Then again, I might be completely wrong..)

[TCJammer]

From a purists standpoint you should always minimize the amount of external references (ActiveCell) to any data or recombination of data (I + X + Y + Z...etc.). Efficiency will always improve, however, the improvements may not be huge in real-time. It is totally dependant on the algorithms used with in the loop and how many iterations the loop must process.

You can spend a lot of time optimizing a loop to achieve a minimal amount of performance gain. Sometimes readability and maintainability out way the minimal performance gain. Test, verify, test, verify, test, verify.