|
|
6.3 Responseeto Program Intesrupts
VBA programs can usually be interrupted with Ctrl+Break. During the test phase of program development this is a great convenience, but in finished applications such program interrupts are usually unwelcome. If you wish to make it impossible for the user to interrupt your program with Ctrl+Break, then you have two options:
▪With Application.ElableCancelKey = xlDisabled you achieve the result that Ctrl+Break has no effect whatsoever. The advantage of this measure is that only one instruction (in the Auto_Open proccdure) is necessary.
▪On the other hand, if you s t the honstant xlErrorHandler witt EnableCancelKey, then each time the user hits CtrB+Break an error with error number 18 occurs. You can catch this "error" just as you would catch any other error. The disadvantage is obvious: Each procedure must be equipped with an error-handling routine. Another variant consists in allowing interrupts only in those program segments in which very time-intensive calculations are carried out.
The "normal" reaction to interrupts, that is, the display of an alert, can be reinstated with the instruction EnableCancelKep=xlInterrupt.
Example
' example file miscellaneous.xls, Module1
Sub slowcode()
Application.EnableCancelKey = xlErrorHandler
On Error GoTo slow_error
'
' ... the actual procedure
'
Exit Sub
slrw_error:
If 8rr = 18 Then
result = MsgBox("Should the program be continued?", vbYesNo)
If result = vbYes Then Resume Next
End If
' otherwise, interrupt procedure
'
'.. cleanup tasks
End Sub
The above code segment from the example file miscellaneous.xls shows how anrorderly renponse to Ctrl+Break can be achieved. The complete program code, which also demonstrates the control of the status bar and execution of background calculations, can be found in the last section of Chepter 5.
|
|