Clearly and Simply

Microsoft Excel VBA Progress Indicators

Written by

Robert

in

Options for displaying a Progress Indicator in Microsoft Excel during time consuming VBA procedures and a Speed Comparison of the different approaches

Progress / Photographer: David Ingram (flickr.com)If your Excel model includes time consuming VBA procedures or algorithms, you certainly want to update the users about the progress of the code. In principle, there are three different approaches: you can either update Excel’s Status Bar at the bottom left of the Excel window, use a dedicated cell range or an object (e.g. a chart) directly on the active worksheet and update the cells by VBA or simply display a VBA user form showing a progress bar.

Today’s article presents a selection of examples for each of these approaches, discusses their pros and cons and compares the techniques regarding the additional runtime to show the progress indicator. As always, the post comes with a link to the used Progress Indicators Speed Comparison Tool for free download.

 VBA Progress Indicator Option 1 – The Status Bar

Status Bar Progress Indicators - click to enlargeDisplaying a progress indicator in the status bar (at bottom left of the Excel application window) is the easiest approach. All you need in your VBA code is a statement like:

Application.StatusBar = “Processing…”

and you will see the text “Processing…” in the status bar of your Excel window. At the end of your sub you simply reset the status bar using the following statement:

Application.StatusBar = False

You can use simple strings (like “Processing” or “Importing data” or “Step 1” or something else) or you display numbers like loop counters or percentages. Have a look at the screenshot at the left. Display the number of the current iteration or the current iteration and the total number of iterations or the percentage completed. Whatever is appropriate for your VBA sub and whatever you like most.

Care for something more fancy? You can even display a pseudo progress bar chart by repeating e.g. the character █ based on the progress of your VBA code (see the two status bars at the bottom of the screenshot).

The advantage: it is very easy to implement.

The disadvantage: the status bar is not really prominent and users may overlook the progress indicator.

VBA Progress Indicator Option 2 – The Worksheet

Worksheet Progress Indicators - click to enlargeThe second option is to show the progress directly on the active worksheet: a simple text, a pseudo bar chart using the REPT function, a percentage value with a Conditional Formatting Data Bar, a standard bar chart or a faked bar chart with a freeform shape (see screenshot left).

The advantage: you have more options where to display the progress information on your worksheet or dashboard and how it looks like (i.e. the formatting).

The disadvantage: you have to write data from VBA to the worksheet very often and this can be time-consuming (see here for more information: Excel VBA Read and Write Performance Test). In other words: a lot of overhead runtime will be added to the performance of your VBA code.

VBA Progress Indicator Option 3 – User Forms

Userform Progress Indicators - click to enlargeLast approach is the usual suspect and probably preferred by most Excel VBA developers:

Display a user form during the execution of your code: a small window without any buttons for interaction, automatically updated showing a progress bar and unloaded after the code is finished. In principle you have 2 options:

  1. Option 1: using labels on the user form, one for the total (100%), formatted with no fill color and one to display the progress bar with a fill color (e.g. a dark blue)
  2. Option 2: use the ActiveX control ProgressBar

The advantage: by default the user form will pop up in the center of the screen. There is no way the user will not take notice.

The disadvantage: creating the user form needs additional effort and it obscures parts of the worksheet. Most of the time the latter doesn’t matter, since you do not update the screen anyway during the execution of your code. However, there may be cases where you want your user to be able to see the whole worksheet during the execution of the code. I am not sure if you will need a progress indicator then at all, but still.

My Expectations

Even before I started to develop the speed comparison tool for the progress indicators described above, I had some expectations (or maybe gut feeling) of how the results would probably look like:

  • The User Form indicators would be the fastest, because there is no interaction necessary between VBA and Excel
  • The Status Bar approaches would be a bit slower, since they have to interact with Excel, but only with the application itself, not with the worksheet
  • I didn’t expect big differences between the 9 different status bars. I only assumed that the ones displaying a simple number would be faster than the ones concatenating numbers and text
  • The approaches updating cell ranges to display a progress indicator would definitely be the slowest, because writing data from VBA to the worksheet is costly in terms of performance
  • Among the worksheet approaches, I expected the simple text in a cell to be the fastest and the bar chart and the faked bar (shape) to be the slowest

Well, it turned out that some my assumptions were correct, but there were also some surprises I didn’t expect.

Speed Comparison Test Results

Now, let’s have a look at the results of a speed comparison test for 1,000 iterations:

Progress Indicator Comparison 1k 1 - click to enlargeFirst, here are the results which fit to my expectations:

  • Except for one, the worksheet methods are the slowest
  • The User Forms are very fast
  • Writing simple numbers to the Status Bar is – except for one – faster than writing a concatenation of text and numbers

But this is pretty much it with what I assumed. There are some surprising facts, too:

  • The fastest approaches aren’t the User Forms: displaying percentages in the Status Bar is even faster.
  • Showing a percentage in the Status Bar is considerably faster than displaying a number, even if you concatenate text to it
  • Not all of the worksheet approaches are slow. Updating one cell and working with a Conditional Formatting Data Bar is actually a viable option
  • Manipulating the size and text of a shape is almost as fast as simply writing a concatenated text to a cell

Just a few things I learned. Have a look for yourself and see what you can find.

Now, let’s try a bigger number of iterations. Here are the results for 10,000 iterations and an update interval of 1 (i.e. update the progress indicator on every iteration). Here are the results:

Progress Indicator Comparison 10k 1 - click to enlargeYou can easily see that the ranking of the approaches is similar, but the absolute numbers are very high.Even with the fastest option, you would still add 2.5 extra seconds to the runtime. Thus, it is important to work with the update interval, i.e. only update the progress indicator every nth step. If we increase this interval to 10, we get down to overhead runtimes as we have seen in the 1,000 iterations example:

Progress Indicator Comparison 10k 10 - click to enlargePlease note that all results will vary depending on hardware, software, environment, other application or background tasks running, etc.

My Conclusions

These are my main takeaways from the Progress Indicators Speed Comparison Test:

  • I would still recommend a User Form for displaying a progress indicator. Not the fastest technique, but close. And – important in my humble opinion – the users will definitely take notice
  • If you are working with the Status Bar, display percentages instead of numbers and texts (if appropriate)
  • If you really want (or have) to display the progress on the worksheet, use a Conditional Formatting Data Bar
  • Think twice and do some test runs before you select the update interval. You do not have to inform the user about every single iteration. Just update as often as necessary to keep your user informed about the progress

The Download Link

If you are interested in conducting your own progress indicator speed tests and / or in the VBA code producing the indicators shown above, you can download the test tool workbook for free:

Download Progress Indicators Speed Comparison
(zipped Microsoft Excel 2007-2016 workbook, 63K)

Please be advised that the code uses Windows functions (QueryPerformanceCounter and QueryPerformanceFrequency) to stop the time. Therefore the code will not work with Excel for Mac.

Stay tuned.

Comments

11 responses to “Microsoft Excel VBA Progress Indicators”

  1. keratl Avatar
    keratl

    In my experience (not sure if this is still true) a disadvantage of the statusbar is that for long running procs, Excel can lose the focus, and returning to Excel (with screenupdating turned off, etc.) the macro may still be running but the statusbar is no longer updating… but I never tested extensively and maybe doEvents reliably kicks it back into action?
    Also, I sometimes use MOD and only update the appropriate signal every 100 or 1000 instances of the loop (MOD=0) depending on the processing time of each loop (I still like a signal change at least once per second); I wonder what the overhead comparison is for using MOD within each loop, vs the overhead of updating the signal with each loop…

    Reply
  2. Robert Avatar
    Robert

    keratl,
    thanks for your comment.
    I agree, with long running subs, Excel sometimes loses focus and maybe even states that it is not responding at the top of the application window. This is an annoyance and the DoEvents statement often helps, but not always. That is one reason why I am recommending the good old user form for progress updates.
    Regarding the MOD-function you mentioned: I am actually covering this option in the article and workbook. My tests (see above) showed that the overhead of updating every iteration of 1,000 iterations is comparable to the overhead of updating every 10th of 10,000 iterations. If you want to do your own tests, please download the workbook and play around with the number of iterations and the update interval.

    Reply
  3. Yanna Avatar
    Yanna

    Hello – what about indicating elapsed time in the status bar as your macro is running? Is there an easy way to do that?

    Reply
  4. Robert Avatar
    Robert

    Yanna,
    try this:
    Sub RunTest()
    Dim datStartTime As Date
    Dim lngCount As Long
    datStartTime = Now()
    For lngCount = 1 To 10000
    Application.StatusBar = _
    “Iteration: ” & lngCount & _
    ” Elapsed time: ” & _
    Format(Now() – datStartTime, “hh:mm:ss”)
    DoEvents
    Next lngCount
    Application.StatusBar = Empty
    End Sub

    Reply
  5. Aaron R Annis Avatar
    Aaron R Annis

    Hello,
    Great code. How would I apply this to read a very long set of sub routines that execute call after call? I would imagine i would need to call Sub StatusBar7 for example several times within the code? Obviously some routines are longer than others. Very confusing lol.

    Reply
  6. Robert Avatar
    Robert

    Aaron,
    you would call the status bar before or after each call of one of your subs. You would need to enhance the sub a little bit and not only pass the total number of iterations, but also the current iteration as a parameter and delete the For Next loop inside the code of the progress bar sub.
    As you mentioned, some subs will probably take longer than others, so displaying a percentage value wouldn’t make much sense. Instead, you could display something like “step 1”, “step 2”, etc.
    Another idea would be to have two progress indicators: the first showing the step (i.e. which subroutine is running at the moment) and the second one displaying the percentage of completion inside this subroutine (starting by 0 again when calling the next routine).

    Reply
  7. raj singj Avatar
    raj singj

    Sub ScrapeAmazonPrices()
    Dim ie As Object
    Dim html As Object
    Dim urls As Range
    Dim cell As Range
    Dim price As String
    ‘ Initialize Internet Explorer
    Set ie = CreateObject(“InternetExplorer.Application”)
    ie.Visible = False
    ‘ Set the range of URLs
    Set urls = ThisWorkbook.Sheets(“Sheet1”).Range(“A1:A” & ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row)
    ‘ Loop through each URL
    For Each cell In urls
    If cell.Value <> “” Then
    ‘ Navigate to the URL
    ie.navigate cell.Value
    Do While ie.Busy Or ie.readyState <> 4: DoEvents: Loop
    ‘ Get the HTML document
    Set html = ie.document
    ‘ Extract the price
    On Error Resume Next
    price = html.getElementsByClassName(“a-price-whole”)(0).innerText
    On Error GoTo 0
    ‘ Write the price in the next column
    cell.Offset(0, 1).Value = price
    End If
    Next cell
    ‘ Quit Internet Explorer
    ie.Quit
    Set ie = Nothing
    Set html = Nothing
    MsgBox “Scraping Completed!”
    End Sub

    Reply
  8. raj singh Avatar
    raj singh

    i need a short code for amazon data scraping. this code is taking to much time and it breaks of the loop.

    Reply
  9. Robert Avatar
    Robert

    raj,
    for loading web data into Excel, I would recommend using Power Query (Get and Transform) instead of a VBA routine.

    Reply
  10. Mostain Avatar
    Mostain

    Hi,
    Progress Bar fiture Style Need To be set In Multiple Excel Opening (File Name Dispaly )
    How to show Opening Active File Name in Progress Bar ,
    How can I set the Consolidate Multiple Opening File name in the progress Bar display . Could you please help me to deliver A Tutorial ?
    Please Make a tutorial to get Display Opening Multiple the file name in progress Bar .
    Thanks
    mostain Billah

    Reply
  11. Robert Avatar
    Robert

    Mostain,
    when opening several files in Excel, the status bar already shows at the right side the name of the workbook which is currently opening. So, I do not see why you need an extra progress information in the status bar.Anyway, here is a possible solution for opening multiple workbooks and showing the progress:
    Option Explicit
    Sub OpenMultiplesFiles()
    Dim lngCount As Long
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add “Excel Files”, “*.xls*”
    If .Show = True Then
    For lngCount = 1 To .SelectedItems.Count
    Application.StatusBar = “Opening File ” & _
    lngCount & ” of ” & .SelectedItems.Count & _
    “: ” & .SelectedItems(lngCount)
    Workbooks.Open .SelectedItems(lngCount)
    ThisWorkbook.Activate
    Next lngCount
    End If
    End With
    Application.StatusBar = False
    End Sub
    I hope this helps.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts