Help pushing long string in a cell repeatedly

[Splotly]

I‘m loading about 1,000,000 records from a text file, each about 200 characters long. I have a loop that pushes each one successively into the same cell, does a recalculate to produce a result in another cell, which it then captures and writes to a file.

I was getting out of memory errors and watched the memory usage using task manager. The memory usage was steadily increasing - guess until it hit the limit. But I’m unsure why? And I also noticed that the loop goes quickly for the first few hundred thousand and then slows to around 1/10th the speed. (All the records are about the same length and effectively the same content. I deleted all the calculation cells that refer to the cell that I push the value into, so all that now remains is a loop that pushes a value into a cell - and the behaviour is the same.)

I slammed a bunch of DoEvents in there in the hope that might help, but no joy.

I wondered if it was due to the length of the string, and sure enough when I reduced the length (left 30 characters only pushed into the cell) it worked fine.

Any idea what’s causing it?

Any idea of a decent workaround? I tried using a Name and pushing the string into that - and that worked better - although noticeably slow, and eventually locks my laptop up (the loop does however finish I just have to reboot to regain access to my laptop!).

Any idea much appreciated. (I of course know I could do it all inside VBA without touching the cells, but I’m making use of Excel’s calculation sequencing in the cells that when I reinstate them will depend on the input cell.)

 

[Yongle]

Excel is not clearing its memory stack. Perhaps this workaround will help:
- You say that the macro runs quickly to begin with and then progressively slows down
- Try piecemealing your process to see if that resolves things

Use a master workbook containing a simple macro to ...
open main workbook, load first 100,000 records, call original macro, save & close main
open main workbook, load next 100,000 records, call original macro, save & close main
open main workbook, load next 100,000 records, call original macro, save & close main
etc

 

[drsarao]

What kind of string is pushed into the cell, what kind of calculation is done and what output is expected after recalculation?
Is it possible to do the calculation in VBA itself? Maybe by taking the relevant data for recalculation in variables? And write back the result.
That will be an order of magnitude faster.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of some sample data also showing the expected result. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[offthelip]

I have had problems with this with excel. The way I solved it was to localise which bit of my code was causing the memory leak. I did this by eliminating different bits of the code in turn , I did this by putting in some dummy subroutines that basically did nothing but did allow the rest of the code to run. I found one subroutine that seemed to be causing most of the problem. This routine was called multiple times. When I looked at this subroutine, it defined a temporary array (called Temparr) and then re dimensioned in the routine. It was this array which was causing the problem, . I solved it by putting this statement at the end of the subrouitne:

Erase temparr