0

I have long processes in Excel VBA going through a variety of steps, where I just need users to sit and wait for completion. I've written a progress bar module to reassure them that it's still churning. Typically, it would work like this:

  • Disable screen updating
  • Call progressbar to say what's being done and/or give completion percentage
  • Do something
  • Call progressbar to update step description and/or completion percentage.
  • Do something else
  • Call progressbar again
  • ...
  • Call progressbar to tell the user that the process is complete.

I've tried making the progress window modal, but it halts any processing until the user does something, which is not what I want.

How can I make my progress window "semi-modal", so that it stays displayed until I say so, but doesn't prevent further processing? I want users to be able to switch to a different application, but when they switch back to Excel, I'd like them to see the progress window and nothing else.

Here's the userform:

Userform with title, main caption, progress bar, room for status message and button

And here's my code.

Sub MyProgressBar(ByVal MyProgress As Long, Optional ByVal TotalItems As Long, _
    Optional ByVal StatusMessage As String, _
    Optional ActionTitle As String, Optional PartialCompletion As String, _
    Optional ItemName As String)

Dim MyAdjustedProgress As Double

' You can specify either a simple progress number,
' or a progress number as compared to a total number of items,
' or a progress as a percentage

' set window title if provided; otherwise, keep existing one
If ActionTitle <> "" Then
    dlgMyProgressWindow.Caption = ActionTitle
End If

If PartialCompletion = "" Then PartialCompletion = "100%"
If ItemName = "" Then ItemName = "item"

' Adjust progress according to completion of a specific step
If Right(PartialCompletion, 1) = "%" And IsNumeric(Left(PartialCompletion, Len(PartialCompletion) - 1)) _
                           And CLng(Left(PartialCompletion, Len(PartialCompletion) - 1)) >= 0 _
                           And CLng(Left(PartialCompletion, Len(PartialCompletion) - 1)) <= 100 _
                           Then
    MyAdjustedProgress = MyProgress - 1 + CLng(Left(PartialCompletion, Len(PartialCompletion) - 1)) / 100

Else
    MyAdjustedProgress = MyProgress
End If
                           
Select Case MyProgress
    Case Is < 0
' if Myprogress is negative, display a completion message
            
        On Error Resume Next ' in case the window was not visible yet
        dlgMyProgressWindow.Hide ' Hide modeless window
        On Error GoTo 0
        
        
        If StatusMessage = "" Then StatusMessage = "Complete"
        
        If Not IsMissing(TotalItems) Then
            If TotalItems < 0 Then Exit Sub 'If we change total items to a negative value, just hide the window
        End If
        
        MsgBox prompt:=StatusMessage, Buttons:=vbOKOnly + vbInformation + vbMsgBoxSetForeground, _
               Title:=dlgMyProgressWindow.Caption

' otherwise,
    Case Else
        dlgMyProgressWindow.lbsPleaseWait.visible = True
        dlgMyProgressWindow.lblMyProgressBar.Width = 1 ' reset it to 1 temporarily, in case it's the first time

        dlgMyProgressWindow.lblMyProgressBar.visible = True
         
        ' Show status message if provided
        If StatusMessage <> "" Then
            dlgMyProgressWindow.lblProgressMessage.Caption = StatusMessage
        End If
        
        ' N.B. full bar size is 300
        ' If Total items is not  specified, assume that progress is a percentage
        If TotalItems = 0 Then
            dlgMyProgressWindow.lblMyProgressBar.Width = 3 * (MyAdjustedProgress Mod 100)
        Else
            dlgMyProgressWindow.lblMyProgressBar.Width = 300 * (MyAdjustedProgress / TotalItems)
            dlgMyProgressWindow.lblProgressMessage = _
                "Processing " & ItemName & " " & MyProgress & " of " & TotalItems _
                & vbCr & vbCr & StatusMessage
        End If
        
        On Error Resume Next 'show or just repaint
            dlgMyProgressWindow.Repaint
            dlgMyProgressWindow.Show vbModeless
        On Error GoTo 0
        
End Select

End Sub
Spline68
  • 1
  • 3
  • Do you mean like `ActiveWindow.Visible = False` ? – HackSlash Jul 20 '21 at 18:49
  • Hm, I like the idea, nice and simple :) but some of my processes include copying and pasting, which can only be done if there is a visible active window. – Spline68 Jul 21 '21 at 08:39
  • you rarely ever need to use Copy\Paste. Please read this thread and see if you can use direct assignment. Using the OS clipboard is crazy slow. If you move away from Copy\Paste and in to direct assignment you will see massive performance improvements. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – HackSlash Jul 21 '21 at 16:06
  • @HackSlash yes, I know it's better to avoid it, but I need to copy both cell content and formatting. If there's a way to do that without using the Clipboard, I'd love to hear about it. – Spline68 Jul 22 '21 at 08:46
  • Have a look at the properties of the `Range` object and see you can pick and choose to copy any properties you want leaving behind those you do not. If you end up needing a whole bunch, just make a `Sub` that does what you need so you can call it instead of repeating yourself. https://learn.microsoft.com/en-us/office/vba/api/Excel.Range(object) – HackSlash Jul 22 '21 at 15:54
  • Thanks! I'm actually copying sub-cell-level text formatting too, so that wouldn't help, but still interesting. – Spline68 Jul 23 '21 at 14:14
  • Honestly, the best way to get what you want is to write the interface in VB.NET. You can make a multi-threaded interface that does a bunch of Excel things using the built in interop libraries. It's super simple because all the commands for the Excel Application model are the same. Then you would have real software that is performant. – HackSlash Jul 23 '21 at 15:04

0 Answers0