Excel out of memory error - variant array with 2m+ rows, restart and process end required RRS feed

  • Question

  • Hi Guys

    I thought everyone had already asked all of the VBA questions I could think of but maybe I have come up with a new one. I have a model which uses a lot of data and should be in a database but is in Excel still, sorry! At a couple of points in the code I load a csv file into a variant array, this is where I get my out of memory error, when I try to redim varArray(2m+, 3). I am running Windows 7 and Pro Plus Excel 2010. I can fix by closing Excel, opening task manager and ending the process tree - when I restart the file I get one more go before needing to close again.

    I understand there are options such as splitting the csv into smaller files or moving the whole process to a database but there are reasons that these are not straightforward. Is there anyway of replicating what is happening when I close and end the process tree within the VBA code? I have added erase varArray to the end of a lot of my functions and subs but not sure if this has helped in any way.


    Monday, December 3, 2012 9:55 AM

All replies

  • This is a very good question, and I am having a similar problem, again with a more than a million rows, with a couple of columns. The data structure shouldn't take that many megabytes of memory.
    Tuesday, November 25, 2014 1:22 PM
  • Re:  arrays and out of memory

    Large arrays use a huge amount of memory and Excel has internal memory limits far below what is available in most computers.
    My experience with arrays says:  don't exceed a few hundred thousand elements.
    Numeric arrays are more lenient than String arrays which are better than Variant arrays.
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 8:09 PM
    Tuesday, November 25, 2014 4:12 PM
  • There is a much slower method to read the CSV file, and it is using Line Input statement or Get (when opening as binary).

    Have you tought about these possibilities?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, November 25, 2014 9:59 PM
  • If redim is the problem, then try reading the csv file once to determine the total number of rows then ReDim the array once and read data in.

    Alternatively open the csv file into a new worksheet or workbook (or PowerPivot) and process the data from there.

    You a re right though, for this amount of data you should be using a database, I mean SQL Express is a free license!

    Felipe's idea of reading line by line would also work as you can split the data into at least two arrays.As Jim said, try to use numeric and string arrays where possible rather than variants.

    Obviously all these ideas are likely to run slower than input in one go.

    Rod Gill
    Author of the one and only Project VBA Book

    Friday, November 28, 2014 8:08 PM