Results 1 to 5 of 5

Thread: Maximum Size for a 2 dimensional array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Maximum Size for a 2 dimensional array

    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.

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Maximum Size for a 2 dimensional array

    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
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,190
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Maximum Size for a 2 dimensional array

    If you are using a dictionary why not store the values associated with each key in the dictionary in separate arrays?
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maximum Size for a 2 dimensional array

    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.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,190
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Maximum Size for a 2 dimensional array

    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.
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •