How should I Notify the user during a long process?

Accepted answer +8 upvotes

You can use the status bar in Excel to do this:

Application.StatusBar = "status message"

Here is an example on how to implement this: http://www.vbaexpress.com/kb/getarticle.php?kb_id=87

Below is the code from the site (added line break to make is easier to read):

Sub StatusBar()

Dim x As Integer
Dim MyTimer As Double

'Change this loop as needed.
For x = 1 To 250
    'Dummy Loop here just to waste time.
    'Replace this loop with your actual code.
    MyTimer = Timer
    Do
        Loop While Timer - MyTimer < 0.03
        Application.StatusBar = _
        "Progress: " & x & " of 250: " & Format(x / 250, "Percent")
    DoEvents
Next x

Application.StatusBar = False

End Sub

UPDATE: I do want to add that updating the status bar will result in a sizable hit in performance (quite a bit actually), so you should only update it in approriate intervals. Here’s an example of what I mean (I use MOD here to ensure we only increment each 1000):

Sub test()

Dim i As Long
Dim temp As String

For i = 1 To 1000000
    temp = "testing 123, testing 123"
    If i Mod 1000 = 0 Then
        Application.StatusBar = "Processing " & i & "/1,000,000..."
    End If
Next

Application.StatusBar = "Ready"

End Sub

Also note that you want to reset the text to “Ready” otherwise it’ll be left as if it were in the loop.