CODEWITHSUNDEEP
vbaexcel
Vityata
Vityata

Reputation: 43585

VBA - Application.EnableCancelKey is acting somehow strange

Pretty much the question is the following - why in the immediate window I get this:

Application.EnableCancelKey = 2
?Application.EnableCancelKey
1

The last line should be 2 or am I missing something? Thanks!

Upvotes: 1

Views: 560

Answers (1)

Pierre Chevallier
Pierre Chevallier

Reputation: 754

Edit:

The basis is that the enum xlErrorHandler states that "The interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement.", you need an error handler to use that enum (xlErrorHandler or 2).

Since some statements are not possible within the immediate window, and On Error GoTo is one of these statements, you cannot have an error handler in the immediate window and thus change the value of the EnableCancelKey to 2. Hence Excel switches it automaticaly to 1, giving you the value 1 when you ask it to display the value.

The only solution would be to use a sub.

Original reply:

Ok, I used the code in the documentation provided on the MSDN and edited a few things to test it with the following code.

To explain shortly I stopped the execution by pressing just once the "ESC" button and thus preventing the textbox to be exited. You can watch in the Immediate Window that the last EnableCancelKey has changed in value normaly.

Sub Test1()

Debug.Print " Before execution result : " & Application.EnableCancelKey

On Error GoTo handleCancel
Application.EnableCancelKey = 2 'xlErrorHandler
Debug.Print " Regular execution result : " & Application.EnableCancelKey

For x = 1 To 10000  ' Do something 1,000,000 times (long!)
    Debug.Print "Test"
Next x

handleCancel:
If Err = 18 Then
    Debug.Print "Aborted macro result : " & Application.EnableCancelKey
    MsgBox "You cancelled"
End If

End Sub

Hope this helps, I got the result expected.

You have to write an Error Handler to get the 2 value, else the code cannot catch the error, that's why you get the 1 every time you were executing the macro.

Since the description of the enum xlErrorHandler states that "The interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement.", you need an error handler to use that enum.

With this it should work:

Sub test2()

On Error GoTo theEnd
Application.EnableCancelKey = 2
Debug.Print Application.EnableCancelKey

theEnd:
End Sub

Upvotes: 1

Related Questions