How to apply "ProgressMeter" Macro

[Tim Hanson]

I am having trouble applying the macro "ProgressMeter", it gives a progress bar in the status bar broken up over 10 time intervals, I would like to use it on some of my long macros or series of macros the problem is how to apply it, if I use it like this

Sub XXX()
Call ProgressMeter
Call This
Call That
Call Other
End Sub

Then "ProgressMeter" runs first independent of the other macros defeating it purpose of giving a status of the percent done of the run time of the following macros

Does anyone have ideas on how to apply "ProgressMeter" is the proper way I cant figure it out

Thanks

Sub ProgressMeter()
     
  Dim i As Integer
  Dim sMessage As String
   
  For i = 1 To 10
    If i = 1 Then
      sMessage = "10%"
     ElseIf i = 2 Then
      sMessage = "20%"
    ElseIf i = 3 Then
      sMessage = "30%"
    ElseIf i = 4 Then
      sMessage = "40%"
    ElseIf i = 5 Then
      sMessage = "50%"
    ElseIf i = 6 Then
      sMessage = "60%"
    ElseIf i = 7 Then
      sMessage = "70%"
    ElseIf i = 8 Then
      sMessage = "80%"
    ElseIf i = 9 Then
      sMessage = "90%"
    ElseIf i = 10 Then
      sMessage = "Done"
    End If
    Call LjmStatusBarProgressMeter(i, 10, sMessage)
    Application.Wait Now + TimeValue("00:0:01")
  Next i
Application.DisplayStatusBar = False
     
End Sub

Sub StatusBarProgressMeter(iValue As Integer, iValueMax As Integer, sMessage As String)
  'This displays a progress bar on the 'Status' Bar
  Dim lWord As Long
   
  ' make StatusBar visible
  Application.DisplayStatusBar = True
     
  lWord = 9609     'Filled in big squares
  lWord = &H22A1   'Empty little squares
  lWord = &H2610   'Empty big squares
     
  'Output the Status Bar
  Application.StatusBar = String(iValue, ChrW(9609)) & String(iValueMax - iValue, ChrW(lWord)) & "  " & sMessage
  'Application.StatusBar = String(iValue, ChrW(9609)) & "  " & sMessage
 
End Sub

 

[Luke M]

VBA does not support multi-thread running, everything is run sequentially. So, if you want to call some code to update a progress bar, it usually needs to be placed within a loop that updates the bar on each loop.

Some examples:
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/
http://support.microsoft.com/kb/211736

 

[Marc L]

Sub DemoStatusBar()
    With Application
        For L% = 1 To 10
            .StatusBar = "        " & L * 10 & "%":  .Wait Now + TimeValue("0:0:1")
        Next
            .StatusBar = False
    End With
End Sub

 

[Tim Hanson]

I think I figured it out in the macro I posted you place the call or code like so

Call LjmStatusBarProgressMeter(i, 10, sMessage)
    Application.Wait Now + TimeValue("00:0:01")

'PLACE CODE OR CALL HERE

Next i

or in Marc L's code

Sub DemoStatusBar()
   With Application
       For L% = 1 To 10
            .StatusBar = "        " & L * 10 & "%":  .Wait Now + TimeValue("0:0:1")

        'PLACE CODE OR CALL HERE

       Next
            .StatusBar = False
   EndWith
EndSub

Or would I be executing the code every time I looped?

 

[Luke M]

Both those examples already have the loops, used by the For...Next commands. So, basic structure is

For ____
UpdateStatusBar
Wait some amount of time
Do stuff
Next

 

[NARAYANK991]

Hi Tim ,

You may find this helpful.

http://ramblings.mcpher.com/Home/excelquirks/a-general-progress-timer

Narayan

 

[Marc L]

Wait instruction in my sample is just to pause the statusbar display !

So no need in a real process …
​