UserForms - Timer & Progress Bar

[DD1]

I am new to UserForms and clocks/timers and am having trouble coming to grips with them. I have searched high and low on the internet but I can’t find exactly what I am after and I don’t know enough to modify what I have found to suit my purpose. I seem to have found everything but what I am after. Any help would be much appreciated.

I want to do 2 things.

Firstly, I want to display a TIMER in a UserForm. The timer would start as soon as the user presses a button which copies a formula down to the end of column of data.
The timer would be displayed until Excel finishes calculating and then it would disappear.
The code to copy the formula down is just a few lines, but 99.9% of the time the macro takes to complete is due to the time it takes Excel to calculate/re-calculate.


Secondly, I want to display a progress bar which updates as the time elapses.
I have found progress bars which update based on a repetitive loop – e.g. entering random numbers in a specified number of rows and columns, but I can’t work out:

1. How to make a progress bar update based on time elapsed. I know how long the macro takes to run on average, so that would essentially be 100%; and
2. How to get it to update while Excel is calculating. During this time it just freezes.

The attached file contains a small sample of my data. The full source data file is >200K rows and takes about 25 minutes to run/calculate which is why I want a timer and progress bar.
It contains a standard VBA module to copy and paste a formula down to the end of column of data.

I don’t know if it helps, but I have included a UserForm for a progress bar, but it is based on a repetitive loop that inserts random numbers. I want to modify this to be based on time. On my PC, the macro takes about 24 seconds to run and for Excel to finish calculating.

I haven’t included a UserForm for a Timer.

In order to get the timer and progress bar to update while Excel is calculating, I am not sure whether the UserForm needs to call the macro in the standard VBA module or vice versa. I’m very confused.

Thanks for taking the time to read this. If you can help, I would be most grateful.

Many thanks
David ExcelForum Sample File - Timer & Progress Bar.xlsm

[LJMetzger]

Hi DD1,

There are two files attached. One that runs based on a preset time. The other runs based on a present number of events such as counts or rows to be processed. The files are almost identical. I decided to break them up into two files, because I thought the code would be too difficult to understand if I was trying to be all things to all people.

Ordinary Module Code follows for the file based on time. The code for the file based on a number of events is also included. If the item in Blue in the UserForm Module is changed, then the file will run on the number of Events. The items at the top of the Ordinary Code module in red are CONSTANTS that can be changed to modify the look, feel, and performance of the file.

Please Login or Register  to view this content.

UserForm code follows:

Please Login or Register  to view this content.

Ordinary Code module code:

Please Login or Register  to view this content.

Lewis

[DD1]

Hi Lewis,

Thanks so much for replying to my rather lengthy post.

This is so far above my head. I really need to do a course, but I'm having trouble finding one where I live.

I have copied my original data into your "ExcelForumUserFormTimerAndProgressBarBasedOnTimes-2015-04-03-19-18" file.

Would you mind letting me know where I insert my macro to copy the formulas down?

What I'm trying to achieve is:

• When the user presses the button to copy the formulas down - column L in the 'Pivot Tables' worksheet
• Firstly your macro kicks off to run the progress bar
• And then immediately after that happens, the formulas are copied down. It takes a long time to calculate, but while it is doing that, I would like your progress bar to continue updating.

I can't get the progress bar to update while Excel is calculating. I understand this should be possible and maybe the UserForm has to be non-modal?

Many thanks Lewis.
Regards,
David

[LJMetzger]

Hi David,

Integrating your code with the Progress Bar proved more difficult that I thought it would be.

Two VBA macros are not allowed to run at the same time (in parallel). If coded properly, there are techniques that can be used to make them appear to run in parallel.

The problem is that your code is essentially one VBA statement. As such, while it is replicating the formulas, the Progress Bar (UserForm) is locked out. For both to appear to run simultaneously I had to break your replication formula into pieces. Before I did that I tried to optimize your code, and benchmarked the time to replcate 995 lines (from line 6 to 1000) on my old Vista 32 bit computer using Excel 2003.

0.14 Seconds - Simulation 01 - Your original code
0.09 Seconds - Simulation 02 - Original with Screen Updating Disabled during replication
0.09 Seconds - Simulation 03 - Replaced your code with 'AutoFill'
0.14 Seconds - Simulation 04 - AutoFill in pieces of 100 rows

This Simulation 04 code is what I used to solve the problem. It will probably run slightly slower than your original, but it will stop when the replication is completed with 100% displayed, and the time for the replication displayed.

Simulation code follows:

Please Login or Register  to view this content.

I will post the updated file with instructions in the next post. It will probably take a few days, as I didn't originally realize that the problem seems to be related to Automatic Calculation and not related to the Replicating of the formulas.

Lewis

[LJMetzger]

Hi David,

I apologize for the delay in updating the files. I thought I was finished before I noticed the 'Automatic Calculation' problems mentioned above.

Attached find two files in a .zip file which are identical except for the following items:
a. The text in the CommandButton on Sheet 'Pivot Table' is different.
b. The routine called by the UserForm module 'UserFormProgressBar' Sub UserForm_Activate() is different.

WARNING: Both file set Excel Calculation Mode to MANUAL. Even so, the file with the array formulas seems to start calculating on its own occassionally. Even though time performance is worse, I prefer the file without the array formulas, because unanticipated 'Automatic Calculation' delays are not a problem.


File ExcelForumUserFormTimerAndProgressBarCopyFormulasDown.xlsm details:
a. The time for the macro to run is 39 seconds on my computer.
b. When the 'CommandButton' is activated, Sub 'Copy_Formulas_Down()' is run.
c. The Sheet 'Pivot Table' Array Formula in cell 'L5' is replicated one row at time.
d. The replicated cell is allowed to calculate.
e. The Progress Bar and Timer is updated after the calculation for the cell is complete.

File ExcelForumUserFormTimerAndProgressBarCalculateMedian.xlsm details:
a. The time for the macro to run is 46 seconds on my computer.
b. The Sheet 'Pivot Table' Array Formula in cell 'L5' is NOT USED.
c. When the 'CommandButton' is activated, Sub 'CalculateMedians()' is run.
d. The median is calculated for each Sheet 'Pivot Table' row by Sub 'CalculateMedians()'.
e. After calculation for a row is completed, the Progress Bar and Timer is updated.

Code excerpts of note in module ModCalculateMedians:

Please Login or Register  to view this content.

Code excerpts of note in module UserFormProgressBar. The last two lines in the Sub control how the calculations are done.

Please Login or Register  to view this content.

Lewis

[DD1]

Hi Lewis,

Firstly I apologise for not replying sooner. For some reason the e-mail notifications for your last 2 responses got caught up in my organisation's spam filter. I have no idea why. It has never happened before. Normally the spam filter works really well and it doesn't capture anything that is really meant for me. It is normally just phishing stuff.

The amount of time you have spent on this and thought that you have put into it is incredible. You have really gone above and beyond. Thank you.

I was under the misapprehension that UserForms ran independently - i.e. used separate memory - and therefore could be run in parallel. It's a shame that this can't be done.

I will definitely be able to use your code.

Thanks again for all your help Lewis. I really appreciate it.
Best wishes
David