How to stop VBA in a Loop

[EagleOne]

2003/2007


I realize that the info provided below is vague. Hitting the ESC key does not stop execution.

The loop is to cycle through a range of cells. As each new cell is accessed the loop causes
movement to/from other sheets to collect/parse data.

Therefore, there is much disk accessing and processor usage. Because of this, (I guess) the
keyboard is given low priority.

Bottom line, I would like to break execution to ascertain the area of the Loop.

The only success I have had is Ctrl-Alt-Delete to get Task Manager and then force Excel down.

Any thoughts appreciated,

EagleOne

 

[David McRitchie]

I don't really have much on debugging but what I have is at
http://www.mvps.org/dmcritchie/excel/getstarted.htm#problems

Here is Chip Pearson's page
VBA Debugging
http://www.cpearson.com/excel/Debug.htm

 

[EagleOne]

I'll look at your site in the morning. Thanks Dave.

 

[blackbox via OfficeKB.com]

Hit ESC twice to interrupt the code and get the End or Debug pop up

 

[Shiva Kumar]

Hello EagleOn,

Sorry to Inturrupt. As per your below message, if we follow we may loose changes made by us in the code.

So, it's better to use Ctrl+Pause Break. If we use this the changes made by us will be safe and stop's the loop.

Shiva.

 

[Mikec007]

Why not code your loop to stop when it reaches the end??

If you are going through a range of cells, find the last row in the
range, and tell the loop next until finalrow.

 

[Walter Briscoe]

In message <[email protected]> of Mon, 22 Nov
2010 09:28:25 in microsoft.public.excel.programming, Mikec007

Why not code your loop to stop when it reaches the end??

If you are going through a range of cells, find the last row in the
range, and tell the loop next until finalrow.

I assume you want to interrupt execution, rather than merely stop -
usually because you have started something you have decided is
unprofitable.

The DoEvents Function yields execution so that the operating system can
process other events.

I am suspicious that it does not always work.
I wait for Internet Explorer to complete with
Do Until Not IE.busy And IE.ReadyState = 4: DoEvents: Loop

I would put DoEvents into your loop at a point where the data is
consistent.

Powering down can also be effective at the cost of risking inconsistent
data.

You may later want to look at Application.OnTime; I don't think it will
help.