Forum Discussion

Jedzrej411's avatar
Jedzrej411
Copper Contributor
Oct 14, 2022

Macro status bar

Hi,

I have quite long makro which runs for about an hour. I want to add a progress bar or some kind of life status update to see if macro doesnt crush or how much time to end. Unfortunately I don't know how to do this so I am waiting for your kind advices 🙂

excel
Macros and VBA

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 14, 2022

    Jedzrej411 

    I assume that the macro contains one or more loops.

    In such a loop, use code like this - the exact contents depend on your code of course.

            Application.StatusBar = "Processing row " & r & " of 10000"

    At the end of the macro, reset the status bar using

        Application.StatusBar = False

    will 

    • jedrzej411's avatar
      jedrzej411
      Copper Contributor
      Oct 14, 2022

      HansVogelaar 

       

      Thanks for such a prompt reply! As I understand I need to paste this code to any loop in the code? I forgot to add that macro also stops screen updating. Will this bar be visible in such a case?

       

      I attach one of my macro loop, another loops are very similar and have only different folder names.

      Public Sub TEST_INV() 
Dim A As Integer
Dim B As Integer
Dim X As String

Application.ScreenUpdating = False

For A = 1 To 100 
X = "STOCK"
S = Sheet2.Range("A1").Value   
d = X & "_" & A
B = ActiveCell.Column
C = B - 1
E = B + 1
    ChDir S & X
    Workbooks.Open Filename:= _
        S & X & "\" & d & ".xlsm"
    Sheets("Sheet1").Select
    If Sheets("Sheet1").ProtectContents Then Sheets("Sheet1").Unprotect
    'Sheets("Sheet1").Unprotect   
    
    ActiveWindow.ActivateNext
    ActiveCell.FormulaR1C1 = _
        "=SUMIF('[" & d & ".xlsm]Sheet1'!C1,RC[-" & C & "],'[" & d & ".xlsm]Sheet1'!C6)"
   
   Selection.AutoFill Destination:=Range(Cells(3, B), Cells(6356, B))
   Range(Cells(3, B), Cells(6356, B)).Select
    
    Windows(d & ".xlsm").Activate
    ActiveWindow.Close SaveChanges:=False
    'ActiveWorkbook.Save
    Cells(3, E).Select
Next A

Application.ScreenUpdating = True

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 14, 2022

        jedrzej411 

        If you have separate loops: yes, in every loop.

        If you have nested loops: in the deepest loop, or if that is unwieldy, in the next outer one.

         

        The status bar should still be updated when ScreenUpdating is set to False, but depending on what's happening in your loops, it might be intermittent. It helps to add a line

         

        DoEvents

         

        in the loops, but that slows down execution too - you'll have to find a compromise.

Resources