Forum Discussion

Roy Brown's avatar
Roy Brown
Copper Contributor
Apr 25, 2020

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

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 ES...
Macros and VBA
RFear's avatar
RFear
Copper Contributor
Oct 20, 2022

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.

  • Nothing_Left_to_Lose's avatar
    Nothing_Left_to_Lose
    Copper Contributor
    Oct 20, 2022

    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)

    • Roy Brown's avatar
      Roy Brown
      Copper Contributor
      Oct 20, 2022

      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)

      🙂😛

      😛

       

Resources