Complete Statusbar Progress Meter

  • Hey, so I recently have been using this in my projects, as I do alot of sql inserts/queries and lots of list formatting where the lists are 50k+ records. I added a ton of comments so hopefully my weird naming scheme will be intuitive for anyone trying to understand it. As it is you can start and end the loop at any row (or any loop values) and it will accurately calculate:

    - the record # out of the total the macro is on
    - total macro run time
    - estimated time left
    - percent of records completed
    - overall average records/second
    - last 10 seconds average records/second
    - minutes and seconds automatically formatted for timeleft and run time
    - everything rounded and formatted so things like '1.10' dont become '1.1' and cause 'blurring' of the text in the statusbar

    I know its rather long for a status bar progress meter... but if anyone can see any improvements or ways to cut it down pls tell me!

    Also, dont worry about the length slowing your macro down... with this empty template it only takes 20 seconds for 100,000 loops on my computer.

  • Re: Complete Statusbar Progress Meter

    and of course, you can use the exact same calculations and simply change the output to something else besides the statusbar, say cell A1 or a userform ;) Cant say how fast that is tho

  • Re: Complete Statusbar Progress Meter

    Very welcome :)

    I've used this site so much for help with excel and vba over the last year or so its not even funny, time to give a little back when I can ::D

    btw, a little tip on the use for anyone who might need it - if you're formatting a lot of rows, you can set lower to the starting row # and refer to "theloop" in your cell statements "Cells(theloop, 1)", which will perform your main loop code on every cell in column A from the value of 'lower' to 'upper'.

  • Re: Complete Statusbar Progress Meter

    New Version:

    - Apparently sped up the macro by ~ 200 times.... lol. It now does an empty loop of 1 million records in just over one second on my machine. Who knew changing only a couple variants to their proper variable type made that much of a difference? I was aware they were slower, but WOW!
    - Added a timer for the time between statusbar updates, now it will update at 1 second, .5 seconds, 3 seconds, or whatever is set.
    - Added a few misc. format options for displaying.

    If a mod could maybe merge this with the first post, or edit the first post to point to this one I would very much appreciate it!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!