Maximum size of a VBA Array - Excel 2007

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Maximum size of a VBA Array - Excel 2007

Post by 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?

User avatar
mbarron
2StarLounger
Posts: 112
Joined: 25 Jan 2010, 20:19

Re: Maximum size of a VBA Array - Excel 2007

Post by mbarron »

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

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by 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
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Maximum size of a VBA Array - Excel 2007

Post by steveh »

WebGenii wrote: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 :grin: ), 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;
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Re: Maximum size of a VBA Array - Excel 2007

Post by Goshute »

steveh wrote:
WebGenii wrote: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...?
Goshute
I float in liquid gardens

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by WebGenii »

Correct - a VB Array

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Maximum size of a VBA Array - Excel 2007

Post by 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?
Regards,
Rory

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by 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.

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Maximum size of a VBA Array - Excel 2007

Post by 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... ;)
Regards,
Rory

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by 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
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by WebGenii »

rory wrote: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
NewLounger
Posts: 5
Joined: 18 Feb 2010, 14:32

Re: Maximum size of a VBA Array - Excel 2007

Post by 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.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Maximum size of a VBA Array - Excel 2007

Post by HansV »

Hi TCJammer,

Welcome to Eileen's Lounge!
Best wishes,
Hans

TCJammer
NewLounger
Posts: 5
Joined: 18 Feb 2010, 14:32

Re: Maximum size of a VBA Array - Excel 2007

Post by TCJammer »

Thank you, good to be here.

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by WebGenii »

TCJammer wrote: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.

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Maximum size of a VBA Array - Excel 2007

Post by 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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Maximum size of a VBA Array - Excel 2007

Post by HansV »

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

WebGenii
StarLounger
Posts: 58
Joined: 26 Jan 2010, 18:21

Re: Maximum size of a VBA Array - Excel 2007

Post by WebGenii »

agibsonsw wrote: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 :hairout: - surely that's much better.

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Maximum size of a VBA Array - Excel 2007

Post by 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..)
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

TCJammer
NewLounger
Posts: 5
Joined: 18 Feb 2010, 14:32

Re: Maximum size of a VBA Array - Excel 2007

Post by 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.