Why does ESC speed up VBA operation in Excel under certain circumstances?

Occasional Contributor

I have just experienced this, and I know there are quite a few references to the phenomenon on the web, so it’s not just me, but no-one has actually answered it.

The normal result of pressing ESC when a VBA macro is running is to halt it. However, when you are long into an Excel session, and press it when watching a macro execute slowly, the first press of it just speeds up the macro, and it takes a second press to halt the macro.

 

Most of the ‘answers’ about this on the web request code samples; but it’s not a code thing, it’s an Excel environment thing. As shown by if I exit Excel completely, and then start It again with the same .xlsm, the issue does not happen. And the macro goes faster anyway.

 

But when the issue is occurring, I can close the .xlsm and reload it, with the Excel session kept alive throughout this by a second spreadsheet I am working on, and the issue will continue; hence me thinking it’s an environment thing.

 

The .xlsm is a compact 200KB, but it is fed from a 3MB ‘mother ship’ conventional Excel spreadsheet. But I just copy from that and paste into the .xlsm, so they aren’t linked in any way.

 

But obviously, as I can be working in both spreadsheets at once, each can be building up an Undo stack, and no doubt other things are going on it the environment also.

 

The speed up is about 50%; the Excel is 2013; the laptop it is running on is a corporate 64-bit one with 4GB memory, running Windows 7 Enterprise.

The ‘mother ship‘ is our complete set of scripts from a Posix server (about 20,000 rows) with processing so I can extract the description line from each, and the calls that each script makes to other scripts. The .xlsm holds copies of these two extracts (400 rows max) and allows us to build a Bill of Materials-like process tree, upwards or downwards, from any given script, so we can see the relationships between them.

 

Anything else you would like to know, please ask.

3 Replies

@Roy Brown I don't have an answer but am experiencing the same issue. Except in my case, hitting ESC will speed up the (VBA) code execution but produce incorrect results. My VBA is simply looking for a match between one table and another. Running slowly, matches are performed accurately but slowly. Hitting ESC, the VBA completes quickly but the match answers are inaccurate.

@RFear 

 

I would guess that Roy Brown, after 2 1/2 years, is probably not still reading these posts.

 

There is a feature in VBA called "EnableCancelKey".

FWIW, the following is from the Excel help file...

 

Excel Developer Reference
Application.EnableCancelKey Property
Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write XlEnableCancelKey.
Syntax
Application.EnableCancelKey
 
Remarks
XlEnableCancelKey can be one of these XlEnableCancelKey constants.
 
xlDisabled. Cancel key trapping is completely disabled.
xlErrorHandler. The interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement. The trappable error code is 18.
xlInterrupt. The current procedure is interrupted, and the user can debug or end the procedure.
Use this property very carefully. If you use xlDisabled, there's no way to interrupt a runaway loop or other non – self-terminating code. Likewise, if you use xlErrorHandler but your error handler always returns using the Resume statement, there's no way to stop runaway code.
The EnableCancelKey property is always reset to xlInterrupt whenever Microsoft Excel returns to the idle state and there's no code running. To trap or disable cancellation in your procedure, you must explicitly change the EnableCancelKey property every time the procedure is called.

'---

 

 Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

@Nothing_Left_to_Lose  @RFear 

 

Quite right, I wasn’t, but I got an email from Microsoft alerting me.

 

So thanks for your answers.

 

Fortunately I haven’t needed to be waiting for them (grin)