Maximum Size for a 2 dimensional array

[Shaft120]

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]

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

 

[Norie]

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

 

[Shaft120]

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]

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.