Search code examples
excelvbauserform

Consistently receiving user input through a long-running procedure (DoEvents or otherwise)

Cleanly cancelling a long API-Ridden procedure is hellish, and I'm attempting to work out the best way to navigate the hellishness.

I'm using excel 2016 (with manual calculations and no screen updates) - I may take some time to attempt to run the procedure on 2010 to see if any issues resolve in the coming days (I'm aware of the slowdowns).

Over time, my procedure LongProcedure has lost its ability to successfully use its cancel feature (presumably due to increasing complexity). It was initially inconsistent and required significant spam-clicking to cancel, and now it fails altogether

Here's the setup:
First, LongProcedure is in a class module LongClass with a public property checked against for early cancelling, allowing it to clean up.

Public Sub LongProcedure()
    ' [Set up some things] '
    For Each ' [Item In Some Large Collection (Est. 300 Items)] '
        ' [Some Code (ETA 5 Seconds) Sprinkled with 3-4 DoEvents] '
        ' [Export workbook (ETA 10 Seconds)] '
        If (cancelLongProcedure) Then Exit For
    Next
    ' [Clean up some things] '
    GeneratorForm.Reset ' Let the UserForm know we're finished
End Sub

Second, I have a UserForm shown from a macro, which instantiates the procedure class, and runs the procedure. It contains a run button, a status label, and a cancel button.

Private MyLong As LongClass

Public Sub ButtonRunLongProcedure_Click()
    Set myLong = New LongClass
    myLong.LongProcedure()
End Sub

So the issue overall is twofold.
The ExportAsFixedFormat call opens a "Publishing..." progress bar which freezes excel for around ten seconds - fine. In all of my efforts, I haven't found a single way to process user input while this is happening.
On top of this, the DoEvents calls seemingly no longer do anything to allow the cancel button to be clicked. The process inconsistently freezes excel, tabs into other open programs, and (when not freezing) updates the status label.

I've Tried:

  • Appending DoEvents to the SetStatusLabel method instead of sprinkling - while the form still often freezes, it otherwise updates the status label consistently (while still not allowing the cancel button)
  • Using winAPI Sleep in place of, and in addition to DoEvents with delays of 1, 5, 10, 50, and 250ms - The form simply stopped updating at all without doevents, and with both it froze more.
  • Using a Do While loop to run DoEvents constantly for one second (Froze)
  • Overriding QueryClose to cancel the form. This one helped significantly. For some reason, the close [x] button can be clicked far more consistently than the userform buttons - Still not as consistently as I'd like. The problem? during publishing, Excel stops responding, and as such, modern windows will end the process if you click the close button twice... without cleanup.
  • Using Application.OnTime to regularly call DoEvents. Didn't seem to improve the situation overall
  • Alt-Tabbing. No, really. for some reason, while alt-tabbing occasionally just makes the UserForm freeze harder, sometimes it makes it stop freezing and update.

This is an issue I'm willing to do significant refactor work for, including smashing up the idea of the long procedure into separate methods, performing setup initially, and cleanup on class termination. I'm looking for something that provides consistent results. - I'll accept anything from excel versions to excel settings to refactors to winAPI calls.

Thanks for any insight into this one.

Solution

  • As it turns out simply combining together some of the useful improvements, along with a new one, made all the difference.

    • QueryClose is up to personal preference. Leave it in to catch more terminations, leave it out to ensure users use the new solution
    • Stick to sprinkling doEvents in places you feel are logical (not just when the status bar updates - like before and after an Application.Calculate call)
    • Optimize the long-running process as best you can, avoiding excel calls

    And, most significantly

    • The integrated cancel key feature (CTRL+Break by default) is significantly more responsive than UserForm buttons and the form close button, without the chance of accidentally ending the excel task.

    Here's the process to polish that for a finished product

    First, set up a debugMode, or the inverse handleErrors, module-level variable to control whether to implement break-to-cancel and error handling. (Error handling will make your code harder to debug, so you'll appreciate the switch)

    If your process is handling errors, you'll set Application.EnableCancelKey to xlErrorHandler, and On Error GoTo [ErrorHandlingLabel]. The error handling label should be directly before cleanup, and immediately set EnableCancelKey to xlDisabled to avoid bugs. Your handler should check the stored Err.Number and act accordingly, before continuing on to the cleanup steps.

    Ensure that if you defer to any other complex vba in your script (such as using Application.Calculate on a sheet with UDFs), you set On Error GoTo 0 beforehand, and On Error GoTo [ErrorHandlingLabel] after, to avoid catching cellbound errors.

    Unfortunately, the drawback is that for the UX to be consistently readable, you'll have to leave the cancel key on xlDisabled until the form is closed.

    And in code:

    Public Sub LongProcedure()
    If handleErrors Then
        On Error GoTo ErrorHandler
        Application.EnableCancelKey = xlErrorHandler
    End If
    ' [Set up some things] '
    For Each ' [Item In Some Large Collection (Est. 300 Items)] '
        ' [Some Code (ETA 5 Seconds) Sprinkled with 3-4 DoEvents] '
        ' [Export workbook (ETA 10 Seconds)] '
    Next
ErrorHandler:
    If handleErrors Then
        Application.EnableCancelKey = xlDisabled
        If (Err.Number <> 0 And Err.Number <> 18) Then
            MsgBox Err.Description, vbOKOnly, "Error " & CStr(Err.Number)
        End If
        Err.Clear
        On Error GoTo 0
    End If
    ' [Clean up some things] '
    GeneratorForm.Reset ' Let the UserForm know we're finished
End Sub

    and in the UserForm

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If MyLong.handleErrors Then Application.EnableCancelKey = xlInterrupt
End Sub

    A small note that this method will likely generate a few bugs you weren't expecting to encounter because the execution jumps directly to the specified label. Your cleanup code will need to have required variables instantiated from the start.

    Overall, once these issues are fixed, this setup ensures that the user can click CTRL+Break as many times as they could possibly want without causing crashes or popups.