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

%3CLINGO-SUB%20id%3D%22lingo-sub-1337334%22%20slang%3D%22en-US%22%3EWhy%20does%20ESC%20speed%20up%20VBA%20operation%20in%20Excel%20under%20certain%20circumstances%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1337334%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20just%20experienced%20this%2C%20and%20I%20know%20there%20are%20quite%20a%20few%20references%20to%20the%20phenomenon%20on%20the%20web%2C%20so%20it%E2%80%99s%20not%20just%20me%2C%20but%20no-one%20has%20actually%20answered%20it.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThe%20normal%20result%20of%20pressing%20ESC%20when%20a%20VBA%20macro%20is%20running%20is%20to%20halt%20it.%20However%2C%20when%20you%20are%20long%20into%20an%20Excel%20session%2C%20and%20press%20it%20when%20watching%20a%20macro%20execute%20slowly%2C%20the%20first%20press%20of%20it%20just%20speeds%20up%20the%20macro%2C%20and%20it%20takes%20a%20second%20press%20to%20halt%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20of%20the%20%E2%80%98answers%E2%80%99%20about%20this%20on%20the%20web%20request%20code%20samples%3B%20but%20it%E2%80%99s%20not%20a%20code%20thing%2C%20it%E2%80%99s%20an%20Excel%20environment%20thing.%20As%20shown%20by%20if%20I%20exit%20Excel%20completely%2C%20and%20then%20start%20It%20again%20with%20the%20same%20.xlsm%2C%20the%20issue%20does%20not%20happen.%20And%20the%20macro%20goes%20faster%20anyway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20the%20issue%20is%20occurring%2C%20I%20can%20close%20the%20.xlsm%20and%20reload%20it%2C%20with%20the%20Excel%20session%20kept%20alive%20throughout%20this%20by%20a%20second%20spreadsheet%20I%20am%20working%20on%2C%20and%20the%20issue%20will%20continue%3B%20hence%20me%20thinking%20it%E2%80%99s%20an%20environment%20thing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20.xlsm%20is%20a%20compact%20200KB%2C%20but%20it%20is%20fed%20from%20a%203MB%20%E2%80%98mother%20ship%E2%80%99%20conventional%20Excel%20spreadsheet.%20But%20I%20just%20copy%20from%20that%20and%20paste%20into%20the%20.xlsm%2C%20so%20they%20aren%E2%80%99t%20linked%20in%20any%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20obviously%2C%20as%20I%20can%20be%20working%20in%20both%20spreadsheets%20at%20once%2C%20each%20can%20be%20building%20up%20an%20Undo%20stack%2C%20and%20no%20doubt%20other%20things%20are%20going%20on%20it%20the%20environment%20also.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20speed%20up%20is%20about%2050%25%3B%20the%20Excel%20is%202013%3B%20the%20laptop%20it%20is%20running%20on%20is%20a%20corporate%2064-bit%20one%20with%204GB%20memory%2C%20running%20Windows%207%20Enterprise.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThe%20%E2%80%98mother%20ship%E2%80%98%20is%20our%20complete%20set%20of%20scripts%20from%20a%20Posix%20server%20(about%2020%2C000%20rows)%20with%20processing%20so%20I%20can%20extract%20the%20description%20line%20from%20each%2C%20and%20the%20calls%20that%20each%20script%20makes%20to%20other%20scripts.%20The%20.xlsm%20holds%20copies%20of%20these%20two%20extracts%20(400%20rows%20max)%20and%20allows%20us%20to%20build%20a%20Bill%20of%20Materials-like%20process%20tree%2C%20upwards%20or%20downwards%2C%20from%20any%20given%20script%2C%20so%20we%20can%20see%20the%20relationships%20between%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnything%20else%20you%20would%20like%20to%20know%2C%20please%20ask.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1337334%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
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.

0 Replies