Maximum Size for a 2 dimensional array


New Member
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.


Well-known Member
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?

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


Well-known Member
If you are using a dictionary why not store the values associated with each key in the dictionary in separate arrays?


New Member
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.


Well-known Member
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.

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...