Use VBA to stop solver updating the status bar

user_2933613 0
Viewed 569

I am running a large optimization process where I would like to check the status. The optimization is based roughly upon a solver loop. I interjected where it is currently in the code by using the status bar. However, solver hijacks the status bar so I can't see my current status bar update. Thoughts on how to suppress solver hijacking the status bar?

solver statusbar vba excel
1 Answers

You can't do that, at least not without some serious Win32 hacking - like, hijacking the Windows message loop and intercepting the messages that update the status bar... that typically doesn't end very well, with access violation crashes and all kinds of things you really don't want to be dealing with in VBA.

What you can do though, is report progress without touching the status bar.

You can do that with a ProgressIndicator modal UserForm (I wrote that article last year).

The crux of it is that you turn your macro into a "dispatcher":

Public Sub MyMacro()
    With ProgressIndicator.Create("DoSolverWork", canCancel:=True)
        .Execute
    End With
End Sub

Now DoSolverWork might look like this:

Public Sub DoSolverWork(ByVal progress As ProgressIndicator)
    Const steps As Long = 10000
    Dim i As Long
    For i = 1 To steps ' whatever your loop does
        If ShouldCancel(progress) Then
            'here more complex worker code could rollback & cleanup
            Exit Sub
        End If

        ' do work here

        progress.Update i / steps
    Next
End Sub

Private Function ShouldCancel(ByVal progress As ProgressIndicator) As Boolean
    If progress.IsCancelRequested Then
        If MsgBox("Cancel this operation?", vbYesNo) = vbYes Then
            ShouldCancel = True
        Else
            progress.AbortCancellation
        End If
    End If
End Function
user_11885130
Related