Progress on Excel status bar

So your macro takes some time to finish and maybe you have called pStarter. It would be nice if the user running the macro would get some idea that something is happening.

pBalls

This little snippet indicates the progress of your procedure on the status bar at the bottom of the Excel window. This is a complete rewrite of the version previously published here. Please note that it requires also the separate routine pSBar to actually write to the status bar.

Since pBalls is called multiple times during the procession you need to set some variables to hold the status of the snippet. In the Declarations section of the module containing pBalls you must declare the following:

The routine itself goes like this:

There are two main ways to utilize the routine. It can show the linear progress of your loop (For…Next) or it can be made to oscillate when the number of iterations is unknown (While…Wend).

Parameter nStep is used during the loop to indicate either the absolute position in the loop or amount of steps to be taken going forward. Omitting nStep will take one step by default. nStep can also be a negative number. Then with a descending loop it projects a normal progress bar. With an ascending loop a negative value will reverse the action on the progress bar. After the loop calling pBalls 0 (zero) will clear the status bar.

The remaining parameters are given only once before the loop to configure the behaviour of the procedure.

Parameter nTotal determines the total amount of steps it takes to complete the progress bar. In oscillation mode nTotal is simply the number of balls to show.

Parameter sText is the descriptive text preceeding the balls on the status bar. Keep it short.

Parameter bPosition as True (default) indicates that nStep is the absolute position of progress within the loop. Setting this False will add the steps given to the previous amount.

Parameter bOscillator as True (default is False) wil set pBalls to the mode where one ball will continuously run from left to right and back until cleared. This is to display progress in loops where the iteration count is unknown.

Place the initiating call before the loop. Within the loop just call with the absolute pointer or without argument. After the loop clear with single argument zero. If your loop is descending (For…Step-1…Next) then give the single argument as negative during the loop.

Here is a sample of a descending loop with absolute pointer:

pSBar

This is the procedure that actually changes the status bar. It can of course also be used by itself. As a bonus it will deter Excel from going to the “Not Responding” coma. Optionally it can also force calculation and/or screen refresh.

 

Updated on February 11, 2017 at 12:34 pm EET

Leave a Reply