The EnableCancelKey property of the Application object determines what will happen when a user interrupts your code. Users can interrupt code by pressing Esc or Cntl+Break. EnableCancelKey has three possible settings:
- xlDisable – Prevents the user from interrupting. I’ve never used this and can’t think of why I ever would.
- xlInterrupt – Normal operation. The debugger is shown and the code is in debug mode at whichever line it happened to be when it was interrupted.
- xlErrorHandler – Raises error number 18 and reacts just like any other error. If you have error handling set up, it’s called.
J-Walk posted about Musical Excel a while back. There was an issue with the midi out API not resetting properly and people could not run the code twice. Mpemba fixed it in the comments with a reset API call, but if the user was to interrupt the code, that API function would never get called.
I downloaded the dueling banjos file from J-Walk, and modified the code. This part was added:
(ByVal hMidiOut As Long) As Long
and this part was modified:
Dim r As Long
On Error GoTo ErrHandler
Application.EnableCancelKey = xlErrorHandler
For r = 2 To Application.CountA(Range(“A:A”))
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
On Error Resume Next
If Err.Number <> 18 Then
Setting EnableCancelKey allows the code to exit gracefully and ensures that the midiOutReset call is always made. This resets the midi out and should prevent the problem of not being able to run the code twice. It shouldn’t really matter whether you have an On Error statement before or after the EnableCancelKey statement.
Actually, this code would exit gracefully except that I have all those ‘Stop’ commands in there. Oddly, when I set EnableCancelKey to xlInterrupt, it works every time I press Cntl+Break (that is, the code stops and the debugger opens). When I set EnableCancelKey to xlErrorHandler, pressing Cntl+Break works about one out of every thousand times. That estimate is based on me pressing it about 100 times during code execution and the code executes all the way through nine out of ten times.
If you have the time, download dueling-banjos.xls, modified the code as I have and see if you can interrupt normally.