Excel freezes while running macro

[Arto Vesterbacka]

Hello,

I tried to find some threads that would cover this, but couldn't. So I'
running a find and replace macro with ~91000 search terms and a list o
~550000 from which to find them. Is it possible that even though tas
manager tells me that excell is "not responding", the macro is stil
running correctly? Task manager shows that excel is using all of on
core out of two, and the used core seems to be switching now and the
and the memory usage is not increasing. I know that if it is runnin
correctly, it would take around 10-48 hours to complete, but if it's no
running and only using resources, I rather would end the run now and tr
another approach

 

[joeu2004]

I tried to find some threads that would cover this, but couldn't.
So I'm running a find and replace macro with ~91000 search terms
and a list of ~550000 from which to find them. Is it possible that
even though task manager tells me that excell is "not responding",
the macro is still running correctly?

Sure. "Not responding" is misleading. It simply means that the Excel
window is not responding to internal window requests. This can happen
because the Excel or VBA thread is busy. Generally, the Excel thread does
not run when the VBA thread is running, except when the VBA thread makes a
request of the Excel thread (like the Find method).

Task manager shows that excel is using all of one core out of two,
and the used core seems to be switching now and then and the memory
usage is not increasing.

That's your proof. Of course, it is possible that VBA or Excel is in an
internal infinite loop or otherwise stuck internally. In that sense, your
macro per se is not "still running" (making progress). That would be a
defect.

Since you have multiple cores, try pausing the macro. How to that varies
from computer to computer. On my computer, I press ctrl+Pause to stop as if
at a break point. I press ctrl+alt+Pause to get a message box with a choice
of Continue, End or Debug.

For the purpose of interrupting the macro at will, it might help if you
insert a statement so that you call to DoEvents occassionally, perhaps every
second ideally. But you do not want to incur the overhead of calling Timer
every iteration. Alternatively, you might determine how long a "typical" or
worst-case Find takes in your situation, then call DoEvents every few times
through the Find loop.

But in any case, VBA will not pause until Excel returns control from the
Find method.



I know that if it is running