Excel pop-up Progress Bar

[spb]

Hello,
I was poking around trying to find some code to create a pop up progress meter when a large spreadsheet i have exceeds say 30 seconds. The file is large with many array formulas and a full file calculation can exceed 3 hours.

I was looking for a visual indicator to show me what's going on and how long it might be before the operation completes.

I have a macro I use to calculate only selected cells (thanks to MrExcel) so I would not want the progress bar to pop up for every operation, only the longer ones.

I have found code for many slick progress bars online but being a VBA novice at best, I don't know how to activate it say when I choose to calculate the sheet or the workbook. I also don't want to inject any significant computational load for the sake of seeing what's going on.

Is this possible? Any ideas?

 

[PatOBrien198]

Simplest and quickest way would be to use the status bar:

something like:

Application.StatusBar = "Currently processing: " & Activesheet.Name & " Progress= " & format(iCount / TotalCount, "##.#%")

then to clear the status bar when done...
Application.StatusBar = False

 

[spb]

What would the full code look like? I can copy and paste but beyond that the code piece to me is completely obver my head.

 

[Caleeco]

I'd be also keen to understand why the code takes 3hours to run. Do you have lots of worksheet interaction? Could you benefit from completing calculations in memory instead? (using arrays)

Caleeco

 

[spb]

I have many tabs with many array formulas over many rows and columns of data. It's just a lot of data. The main data set is about 34,000 rows but the other tabs look at that data set and sometimes to other tabs for calculations, many with arrays. It's just a lot of data doing a lot of complicated things. I've tried to keep it manageable but every time someone asks for some new special summary I add a tab and calculate from the main data set. I have to distribute the file each week and I use a macro to save all the formulas as values then I trim out the tabs that don't need to be distributed and it's useful for the people that use it without the aggravation. That drops it from about 11MB to 3MB. I also save as binary with helps reduce the size.

 

[adrienne0914]

Simplest and quickest way would be to use the status bar:

something like:

Application.StatusBar = "Currently processing: " & Activesheet.Name & " Progress= " & format(iCount / TotalCount, "##.#%")

then to clear the status bar when done...
Application.StatusBar = False

Butting in... I would like to use this. Would you create a macro?