progress indicator in VBA

Question

Hi All,

I have a long-running VBA program and was wondering if there was a way to send progress reports while running. I know you can use msgbox but it stops the program until you clear the message box.

Are there other ways to handle this?

thanks,

rod.

Answer 1

The link provided by Gary:

http://spreadsheetpage.com/index.php/site/tip/displaying\_a\_progress\_indicator/

and that provided by Pete:

http://www.andypope.info/vba/pmeter.htm

are very helpful for creating "professional"Progress Indicators with really minimum effort.

I wasn't aware that such thing is available and easily accessible!  I have been using the classicApplication.StatusBar to show progress in lengthy VBA macros.  That was until now:)

Thank you.

Answer 2

You're welcome - thanks for feeding back.

Pete

Answer 3

Andy pope has a workbook that you can download from here:

http://www.andypope.info/vba/pmeter.htm

which shows a variety of different progress indicators that you can easily implement.

Hope this helps.

Pete

Answer 4

Rod,  I am far from an MVP but for simple stuff I use the Application.StatusBar

(It controls the word "Ready" you see in the lower left corner of the screen)

Suppose I have a proc processing rows 100 to 10,000

For Row = BegRow to EndRow

    'Your App's code here

    'To change the status bar every 500th row

    if row mod 500 = 0 then Application.StatusBar = "Done with Row " & Row & " Out of " & EndRow

Next Row 

Application.StatusBar = False  'Turns text back to "Ready" at processing end

I've found that the changing text draws your eyes to it, so no announcement to look there is even needed.

Also, you did not mention what kind of processing is taking the "long time" so remember two biggies, but easies, for speeding up processing:

Application.ScreenUpdating = False  'dont show updated values in cells 'til the end

Application.CalculationMode = xlManual  'don't recalc values until you have to.

'Your code goes here

Application.CalculationMode = xlAutomatic

Application.ScreenUpdating = True

Answer 5

See:

http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/

---

gsnu201005