VBA : Simple use of the Status Bar, and a Progress Bar solution

[Stormin']

Hi all,

A few months ago I created a pair of public functions to easily update the Status Bar and Progress Bar in MS Access which displays simple information when running code. You can see these over on the Access-Programmers forum: https://www.access-programmers.co.uk...d.php?t=293562

Today I needed them in Excel and have converted them to Excel's VBA environment, so I thought I would share.

The Status Bar is the grey bar at the bottom of the application window. In Excel it is only in the left hand side of this bar. It can easily be missed so this is not intended as an ideal solution for end-users. Instead it is more of a "if you know it's there, it's useful" for attentive users and those using/developing their own VBA code.

Feel free to remove the author info at the beginning, but the notes are useful to keep in.

Status Bar
Straightforward conversion.

Please Login or Register  to view this content.

To update the Status Bar with your chosen text you simply use:

Please Login or Register  to view this content.

Passing no argument (or an empty string) resets the status bar and gives control back to Excel.



Progress Bar
Now, this one is a bit trickier. In Access, there is a native progress bar that you can use. In Excel, there is none... but I still wanted to be able to display progress in a bar. One solution is to use the status bar to mimic a progress bar. I used those weird block ASCII characters I know you've seen but never really had a use for.

Please Login or Register  to view this content.

To use this function you either pass a number or a string, and it will update the progress bar or progress title accordingly.

For a working example you can use the code I used to test the functionality:

Please Login or Register  to view this content.

Examples
Test1:
Test2:
Test3:



Enjoy