escape from vba-loop

[steph]

hi group,

I've got an excel-sheet containing some vba-code. There's one sub that
does some calculations in a loop which can run for some time. My users
now want to be able to escape from this calculation by pressing
<escape> or clicking a button on the sheet. But actually excel is
frozen while calculating.

What is the most common way to achieve this? I guess it must be
possible to solve this with excel-events, but I'm not very experienced
in using them.

thanks for any hints,
stephan

 

[Jim Cone]

You can add a "DoEvents" statement inside the loop.
Excel checks for any instructions coming from Windows when it sees that instruction. Best not to use that on every loop as it is
just another straw on the camels back.

Since the loop takes some time, you need some sort of progress indicator
to let users know something's happening and how long they have to wait.
The DoEvents statement can then be executed when progress is displayed.

You also need error handling code in your procedure to nicely exit the
procedure if escape is pressed.

You have some more work to do. <g>
--
Jim Cone
Portland, Oregon USA



"steph" <[email protected]>
wrote in message
hi group,
I've got an excel-sheet containing some vba-code. There's one sub that
does some calculations in a loop which can run for some time. My users
now want to be able to escape from this calculation by pressing
<escape> or clicking a button on the sheet. But actually excel is
frozen while calculating.
What is the most common way to achieve this? I guess it must be
possible to solve this with excel-events, but I'm not very experienced
in using them.
thanks for any hints,
stephan

 

[Harald Staff]

Hi Stephan

See CheckKey.zip at http://www.oaltd.co.uk/Excel/Default.htm

HTH. Best wishes Harald