Maximum Size for a 2 dimensional array

Shaft120

New Member
Joined
Sep 24, 2009
Messages
14
Hi - Been a while, but I'm hoping to find a way to fix a limitation on a reconciliation tool I designed a while back to compare two extracts from an SQL database.

In particular I'm having trouble on memory limitations when Resizing a Variant array:

ReDim Ukey(1 To MyDict.Count, 1 To ((H - 1) * 3) + 2)

This equates to a Redim of Ukey (1 To 221, 1 To 58589)

Which is c13m values. I thought there was a limitation of about 25m, but I cant recall why I thought that. Anyway, any thoughts on a workaround?

Context is that I have a primary key stored in MyDict and Each files values in a separate Array. I resize this 3rd array to combine both and add a comparison column as well, then parse the data in through loops (tried using Dictionary print previously, but that is even more limited in size, so had to rewrite it).

Thoughts appreciated.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,818
The code below works for me. You can always use worksheet cells with screen updating disabled, but it may be slower.
Could you do it all within SQL?

Code:
Sub Macro1()
Dim ukey, i, j, maxx, maxy
maxx = 1000#
maxy = 100000#
ReDim ukey(1 To maxx, 1 To maxy)
For i = 1 To maxx
    For j = 1 To maxy
        ukey(i, j) = i + j
Next j, i
MsgBox ukey(maxx, maxy)
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
If you are using a dictionary why not store the values associated with each key in the dictionary in separate arrays?
 

Shaft120

New Member
Joined
Sep 24, 2009
Messages
14
Hi Norie. I did originaly design it that way, but dictionary is even more limited in memory printing back to the sheet at the end. In order to keep the maximum possible dataset, i use 2 sourcr arrays and then the dictionary just for primary keys to loop through for its calculations as I populate the final array. Its the last one which was causing the issue, but once populated it's easier to print it into the sheet.

Anyway - I've solved the problem with a workaround. If I declare the array as String, it removes the memory problem. It means I have to know which columns of data to treat as values - so the user has to define these up front before running, but then when I am doing compares on a value column, I can use Val() for that data. Likewise at the end I can convert those columns I know have values in them after they have been put back into the worksheet.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
What problems were you actually having?

I know there's a limit to how much data you can write back to a sheet in one go but I'm not sure that's a memory issue.

When I've run into that problem I've used some novel approaches, seem to recall one time writing the data, as it was being processed, out to a CSV file and then using ADO to read that back in.
 

Forum statistics

Threads
1,084,782
Messages
5,379,843
Members
401,630
Latest member
lisa83rtt

Some videos you may like

This Week's Hot Topics

Top