Help pushing long string in a cell repeatedly

Splotly

New Member
Joined
Dec 11, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,738
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
Erase temparr
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top