Progress Bar in the Status Bar of Excel

[excel_2009]

hi excel gurus,

I have a macro that takes quite some time to run, 3-4 minutes is it possible to have a % progress indicator in the status bar to let me know how much of the process has been completed so far?

Thank you

 

[obiron]

The short answer is yes.

you can use

 Application.StatusBar = intX & "% processed so far"

the longer answer is that is may be a bit more complicated.

Can you measure how far through the process you are (E.g. you know you are processing 50,000 records and you are currently on record 20,000 so you are 40% of the way)

Some processes cannot be interrupted by the status bar (e.g. if you have copied 50,000 formular1c1 cells and are waiting for Excel to resolve the formulas, there is no way to tell how many formulas have been resolved and how many there are to go.

Not all processes will take the same amount of time so calculating a time remaining could be difficult. If you are using finds, sumproducts, Vloolups etc on sorted data then records that appear at the top of the data take substantially less time to resolve than those at the bottom. E.g. I recently posted find functions in batches of 500. the first 500 took a fraction of a second to post and update. The 80th batch (40K lines down) were taking almost 3 seconds to post and update.

 

[AlphaFrog]

Here is a good code example.

Use the Status Bar as a Progress Indicator
http://www.vbaexpress.com/kb/getarticle.php?kb_id=87

 

[VoG]

Take a look at Control the LED Display in the Status Bar http://j-walk.com/ss/excel/files/developer.htm

 

[obiron]

you may also want to see if you can speed your code up.

Unecessary activates and selects slow things down. hiding or deleting lines one at a time is inefficient. It is better to flag the lines for deletion, then sort them and delete them as a single block.

some functions are really slow if used in bulk (sumproduct comes to mind ) You may be better off creating temporary column values to find and match records and then clear up after yourself when finished

If you calulate values with formulas and no longer need the forumulas copy and pastespecial xlvalues so that reorganising the sheets does not cause formulas to re-calculate.

Using these techniques I got an application which was taking 90 minutes to complete to get to the end in under 8

 

[excel_2009]

Hi excel gurus, i've taken your points across and i'm in the process of cleaning up coding, can anyone help advise me on how to improve the efficiency of the coding below:

Sheets("QA").Select
    Range("A:L").Select
    Range("D1").Activate
    Selection.AutoFilter
    
    ActiveSheet.Range("A:L").AutoFilter Field:=11, Criteria1:="2"
    
    ActiveSheet.Range("A:L").AutoFilter Field:=8, Criteria1:="6"
    Selection.Copy
    Sheets("Temp").Select
    
    Range("A1").Select
    ActiveSheet.Paste
    Range("M2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=COUNT(C[-12])"
  
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Home").Select
    Range("B7").Select
    ActiveSheet.Paste
    Sheets("Temp").Select
    Range("A1:M1476").Select
    Range("N2").Activate
    Application.CutCopyMode = False

i dont think there is a quicker method?

thank you