Show Elapsed Time (in realtime) On UserForm While Marco Runs

**nagha3** · 01-29-2020, 01:53 PM

Hello All!!!

I'm trying to wrap my brain around a little problem I'm having. I've create a code to automate some processes in an excel workbook for my office, however it tends to take a little while to iterate and complete the process.

To keep any users of the workbook from freaking out and thinking excel is hanging on them, I've also implemented a progress bar on a Userform.
Untitled.png

While I've got the '% Complete' section figured out, I am trying to also show the elapsed time it is taking to run the macro. My current code simply updates the userform with the elapsed time for the procedure, but I would like to have it constantly updating the elapsed time. Despite countless searching, I can't find anything that gives me insight into how you show the elapsed time in real-time while also letting the macro run in the background.

Any help with this issue would be greatly appreciated. I've attached some of my code in hopes someone out there can help. Thanks in advance!!!

Please Login or Register  to view this content.

**Marc L** · 01-29-2020, 02:03 PM

Hello !

First see how many time needs your procedure without such a progress bar
as often the progress bars are very not smart designed, in fact just a very bad logic of the code author …

I remind a VBA progress bar procedure needed more than a minute to execute
and without the progress bar it lasted less than five seconds !

If your progress bar is not smart, you can replace it just with a classic waiting message
or better think to update the progress bar only when it's necessary, so far so better …

**nagha3** · 01-29-2020, 02:14 PM

The process takes substantial time without the progress bar (typically 4 minutes). This progress bar implementation only adds maybe 5 or 10 seconds to that, which I'm not worried about. What I am worried about is that without a running timer showing while the code executes, users will think the workbook is stuck on calculations and try to break out of the operation.

**Marc L** · 01-29-2020, 06:32 PM

So better than a partial code it seems you missed the top page yellow banner, could be so easier for any helper …

**Leith Ross** · 01-30-2020, 11:04 PM

Hello nagha3,

Updating objects in real time is a bit advanced. Probably why you have not found it your searches. I have put together a UserForm with a progress bar similar to what you posted.

You update the progress bar by calling the macro UpdateProgressBar and passing the completed amount as percentage: .01 to 1. It is up to you to determine the percentage of completion.

The attached workbook has a working example.