Yet Another Progress Bar

See Progress Bars for more information.

I have some applications, like QB SDK that not only take a long time, they also don’t progress at a uniform rate. If I use a standard progress bar, it will whip through the first 50%, sit there for five minutes, then whip through the last half. Since the Quickbooks SDK doesn’t have any callbacks, I can’t report on the progress of that five minutes.

Progress bars should achieve a few goals: They should reassure the user that something is happening and that his computer hasn’t stopped working; they should give the user some indication of how much time is left until the operation is complete; and optionally they should tell the user what’s currently happening. When a progress bar sits on one task for far longer than other tasks, it doesn’t really meet any of these goals. A stalled progress bar could easily be interpreted as a stalled computer and there’s no indication of when it will start moving again, much less when it will complete.

I couldn’t achieve either of the first two goals with VBA, but I was at least able to tell the user what the heck was taking so long. Instead of an animated progress bar, I used a listbox on a userform to announce which step was currently being performed.

To make this type of progress indicator, you only need a userform with a listbox on it. The userform’s ShowModal property must be set to False. This allows code to continue running while the userform is displayed. Then, at certain intervals, you can use the AddItem method of the listbox to display the current task. Your code might look like this:

Sub DoStuff()
   
    Dim ufUpdate As UUpdate
    Dim dtTime As Date
   
    ‘instantiate the userform
   Set ufUpdate = New UUpdate
    ufUpdate.Show
   
    ‘display a step
   ufUpdate.lbxStatus.AddItem “Starting Process…”
   
    ‘Wait for demo purposes
   dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    ufUpdate.lbxStatus.AddItem “Do the next thing…”
   
    dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    ufUpdate.lbxStatus.AddItem “Done!”
   
    dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    Unload ufUpdate
   
    Set ufUpdate = Nothing
   
End Sub

I use ellipses at the end of captions to indicate that there’s more to come. Another method is to load the listbox with all of the tasks and check them off as they complete.

For this method, set the listbox’s ListStyle property to 1 – fmListStyleOption and its MultiSelect property to 1 – fmMultiSelectMulti.

Sub DoStuff()
   
    Dim ufUpdate As UUpdate
    Dim dtTime As Date
   
    ‘instantiate the userform
   Set ufUpdate = New UUpdate
    ufUpdate.lbxStatus.AddItem “Starting Process…”
    ufUpdate.lbxStatus.AddItem “Do the next thing…”
    ufUpdate.lbxStatus.AddItem “Done!”
    ufUpdate.Show
   
    ‘show step as complete
   ufUpdate.lbxStatus.Selected(0) = True
   
    ‘Wait for demo purposes
   dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    ufUpdate.lbxStatus.Selected(1) = True
   
    dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    ufUpdate.lbxStatus.Selected(2) = True
   
    dtTime = Now
    Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:02”)
   
    Unload ufUpdate
   
    Set ufUpdate = Nothing
   
End Sub
Posted in Uncategorized

29 thoughts on “Yet Another Progress Bar

  1. Dick,

    When percent is unavailable, I like your method of update best because not only can I can see the current position, I can also see those left to go.

    If descriptions are meaningful, or I’ve been through this activity countless times before, I can do a mental estimate of time left to go.

  2. Hey VBAers,

    Remember me? I was debating on accepting a job as an Excel programmer or staying in the financial services field. Well, I did it. I took the leap. Turns out that the job will also entail programming Access. I barely know the Excel object model! Is there an Access blog equivelant to this site? Anyone out there familiar with the Access object model? Can you please let me know what objects to focus on because my time to learn is limited. Also by skimming thru a book, it seems like the Access programmer will write code behind Forms and Reports to manipulate them. How else can you use VBA with Access? I always thought Access is only used as a DB. I took the leap in the deep end, now I’m trying to swim. Someone throw me a life saver!

    Macro Man

    p.s. – there’s no Macro Recorder in Access to learn from?!?

  3. Progress can also be shown, without using a user form, through excel’s Application.Statusbar. Although it has limited content, it may satisfy most cases by using qualified expressions, such as:
    Processing record xx out of yyy
    Part 1 of 5: Retrieving data
    zz% complete

  4. I don’t like using the status bar. For one thing, I never notice a message there, and for another, most users are even more obtuse than I am. If I could master the Windows API that will hit them on the head with the keyboard, I’d use that instead of progress bars.

  5. I think I may be missing something obvious here…

    Trying to recreate the example, I have copied the code into a module, created a form called “ufUpdate” (ShowModal property is set to False), inserted a listbox to that called “lbxStatus” and set the listbox’s ListStyle property to “1 – fmListStyleOption” and its MultiSelect property to “1 – fmMultiSelectMulti”.

    The code always fails at the first line (Dim ufUpdate As UUpdate). The error message is “Compile Error: User-defined type not defined”.

    Where / how should UUpdate be defined?

    Jon P – I agree about the status bar, I sometimes see files where someone has decided that it’s a good idea to “sign” their work by adding “Created by myname” to the status bar when the file opens, but they never seem to reset it (either after a set time period, action or when the file closes)… Not sure I like the sound of that API though!

  6. Dick
    The listbox idea is very good. Will use it for my next database project, where time and progress will be anything but linear. Thanks.

  7. I’ve done similar with a textbox, adding items as they are processing with the ellipsis, and changing that to Done when I add the next item. The listbox with checkboxes is an interesting variation.

  8. Dick:

    This is a great piece of code and something that I’ve been looking for. However, I do have a “newbie” question (this is my first vb app to write):

    How do I update the list after certain other processes/subs have completed. I’ve tried using

    ufUpdate.lbxStatus.AddItem “Starting Process…”

    in one of my other subs, but I keep getting “Run-time error ’91’: Object variable or With block variable not set”.

    How do I correctly reference this sub (or am I referencing a form) in order to get the list to update.

    Thanks!

    Josh

  9. Josh: Make ufUpdate a module-level variable. Remove

    Dim ufUpdate As UUpdate

    and put this line at the top of your module

    Public ufUpdate As UUpdate

    That way, all of your procedures will be able to see that variable.

  10. I tried implementing this UserForm in an Excel VBA but somehow when it executes the rest of the code it loses focus and won’t update the UserForm, so the checks don’t appear.
    How to control this ?
    SetFocus doens’t work and gives an error while executing.

    I’m using Excel 2003.

    Thanks in advance,

    Edwin Janssen

  11. Nevermind my question I fixed it with a Repaint command after every checkbox change.
    Is there a better way to do this ?
    Shouldn’t there be a Autoupdate on the userform ?

  12. Is there an easy way of modifying the descriptions in the listbox as the code progresses?
    e.g

    ‘show step as complete
    ufUpdate.lbxStatus(0) = “Starting Process…Done”
    ufUpdate.lbxStatus.Selected(0) = True

    I’ve tried various versions of the above but keep getting a Type Mismatch error.

  13. As Dick mentioned, an ideal status update would include information on the time remaining to complete the process but that information is rarely available — at least not accurately. I find misleading status updates far more — or at least as — annoying as getting no information.

    Here’s a variation of Dick’s implementation that provides some more information at very little cost.

    Recently, I had to provide status updates for a process that included a series of tasks. A few at the start and at the end were non-discretionary but all the others in-between were discretionary. The user could choose to run as many of those tasks as desired.

    So, I created a userform with textboxes and checkboxes laid out vertically. The textboxes contained the names of the non-discretionary tasks and the checkboxes were for the customizable tasks. Once the user selected the tasks of interest and clicked the ‘Get going’ button I left the userform visible. To the right of each textbox and checkbox I added a textbox that contained the time the task was completed.

    Also, on the extreme right of the userform was a large textbox that provided more detailed information about each task. So, the user could judge the speed at which the components that made up each task were being processed.

    Of course, the architecture was “black boxed,” i.e., modularized. The class module exported three methods: UserChoices, MinorUpdate, and MajorUpdate. UserChoices provided the client with information about which custom tasks the user selected. MinorUpdate updated the large textbox on the extreme right of the userform. MajorUpdate updated the major task list by adding the current time to the appropriate textbox. It also cleared the textbox used for the minor updates.

    To me this is one of those many instances where the marginal cost is insignificant and the benefit is so great. Not to mention the “Wow!” factor when not only my direct client contact but also others in the company who use the add-in ask “why can’t others provide the same kind of status updates?”

  14. Internally I record the start and end times of each component in the progress stream and then calculate the duration. In a db table or a very hidden worksheet if using Excel VBA, I record the times for last 5 or 10 executions of the each component. The status window displays the average duration time for each of the components so the user can have a sense for how long each part will take. Since I run the process many times in test before it goes to the user, they start off with average times on their first use of it.

  15. Hey Dick,
    I tried to replicate the same in Access, and it works. However, I’m turning of the echo and warnings as soon as my process starts, so I’m seeing all the updates after the process has stopped :). Any other solutions to have the warnings off and still show updates? Thanks.

  16. A7n9,

    A combination of SetWarnings and Do Events should work for most situations.
    For example:

    SetWarnings False
    ‘action 1
    Do Events
    ‘action 2
    Do Events
    ‘action 3
    Do events
    SetWarnings True

  17. This is plain awesome!! Thanks a ton Dick.

    One small question, why did you use “ufUpdate” to hold an instance of the form? Why not just do this – Userform1.lbxStatus.additem = whatever ?

    Dion

  18. Just wanted to thank you for this excellent code. I’m using it in my projects and have also received great feedback on it! :)

  19. Hi,

    Thank you for this excellent idea. I am using it in my current work. All works well except that the Progress Bar form is not visible…..it comes in grey mode and stays there as if it is waiting to get repainted.

    Thanks again.

  20. Just to add a little more to the above, it shows/works perfectly when I run it in Debug mode.

    Thanks.

  21. This worked great for me… I had to show the form modeless though or else it didn’t let my code finish and I needed a DoEvents after the first selection is updated. The two additions I made to the code were below

        Dim ufUpdate As UUpdate
        Dim dtTime As Date
       
        ‘instantiate the userform
       Set ufUpdate = New UUpdate
        ufUpdate.lbxStatus.AddItem “Started Process…”
        ufUpdate.lbxStatus.AddItem “Refreshed Pivot Table…”
        ufUpdate.lbxStatus.AddItem “New Report Created!”
        ufUpdate.Show vbModeless
       
        ‘show step as complete
       ufUpdate.lbxStatus.Selected(0) = True
        DoEvents


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.