Dealing with user cancellation of slow macro

This post is a little different from usual.  It concerns VBA macros for MS Excel. Writing macros is quite easy, but writing efficient macros that run in an acceptable time requires a few tweaks.

Commonly known tweaks include turning off screen updating (see the code below for an example of how this works) and then updating the statusbar to show the user progress (also in the code below).

However, what often frustrates me is the inelegant ways in which a user can exit a macro which could take hours to run. I struggled a little to find the approach demonstrated below so I thought it might be useful for others.

Basic premise is to use error handling to tell Excel to be on the lookout for keyboard cancellations (escape or ctrl-break) and then handle these gracefully.

Private Sub RunLongMacro()
'declare count variables
Dim count As Integer
Dim Total As Integer
Total = 10
'Set the action on an error. In this case go to the label "handleCancel"
On Error GoTo handleCancel
'Turn off screenupdating to increase run speed and start statusbar reporting to user
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.StatusBar = "Initiliasing..."
'Tell VBA what to do when a cancel key is pressed (Escape or CTRL-BREAK)
'In this case handle the error (Rather than ignore it or present standard error)
Application.EnableCancelKey = xlErrorHandler
'Start the main calculation loop
For count = 1 To Total
'Some additional code might be included here
'Then we calculate the model results
Application.Calculate
'and update the status bar with the count
Application.StatusBar = count & " / " & Total
Next count
'When we're done, we reset the statusbar and screenupdating
Application.StatusBar = False
Application.ScreenUpdating = True
'Next part is the code to handle errors, starting with the specified label
handleCancel:
'If there isn't an error, just skip to the end
'This ensures a single exit point
If Err.Number <> 0 Then
If Err.Number = 18 Then
If MsgBox("You pressed Escape or CTRL-BREAK. Do you want to quit?", vbYesNo, "Quit?") = vbNo Then
Resume
End If
Else: MsgBox "Oops. We encountered an error"
End If
End If
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

2 thoughts on “Dealing with user cancellation of slow macro”

  1. I’m really not a big fan of VBA. Version control is a mess, it often lacks decent object orientation (although it is possible) and you miss out on a lot of features provided by a framework like .NET.

    Furthermore, long running macros should actually be written as a decent COM component that exposes its interface with asynchronous calls, so that the Excel UI isn’t locked up. That should enable the user to continue working on another workbook while the COM component does it stuff with the initial workbook.

    1. Right you are as a developer / programmer. However, VBA has a huge part to play (factually it does, as well as should) in more complex models developed by excel users rather than “real” programmers. There is very little OOP required for 99% of VBA that is still massively useful.

      Incidentally, the Binomial Tree model I built in VBA uses OOP and a linked tree data structure. It’s not the fastest thing on the planet, but the guys who work with me can all hack their way through the basic ideas.

      I’d almost suggest that there are several tiers of the use of VBA and .NET in conjunction with Excel:

      Hacked recorded macros to repeat tasks. Useful but dangerous, ugly and risk-prone. Still makes Excel more useful than without this, and requires no programming knowledge at all
      Coded macros with little structure and manually defined ranges. Possibly the most dangerous, and the one area I don’t think should be promoted at all.
      Well-coded, but basic User defined functions. This is a great space. No risk of deleting cells and ranges or breaking things, but provides additional functionality and encapsulation of more complex tasks. I find this works well when providing a complex model to a client, so the code is hidden in the background and the use of the model is still non-VBA excel based.
      Well coded VBA code, possibly including forms and a UI. Can be useful, but starts to suggest the need for a more structured programming approach.
      Non-VBA, .NET or DLL approach. More advanced, more structured, faster, but much more limited in terms of the number of people who can do this. Often, it becomes clear that the model probably shouldn’t be excel-based in the first place!

      So there really is a range of applications on a continuum of skill-requirements. You and I have already had discussions around hammers and nails for that matter!

Comments are closed.