CODEWITHSUNDEEP
excelvba
Cprog
Cprog

Reputation: 67

Progress bar while running macro

This is a macro that works on all the files of my workbook and executes the macro wrap on all the sheets if the sheet is visible. I wanted to show a progress bar to show the progress while macro is running..

Sub execute()
Application.ScreenUpdating = False
Application.Cursor = xlWait
' makes sure that the statusbar is visible
Application.DisplayStatusBar = True
'add your message to status bar
Application.StatusBar = "Formatting Report..."
userform1.show

    Call Delete_EmptySheets
    Dim WS_Count As Integer
    Dim i As Worksheet

 ' Set WS_Count equal to the number of worksheets in the active
 ' workbook.

 WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.

For Each i In Worksheets
If Not i.Visible = xlSheetVeryHidden Then
  i.Select
  Call wrap
End If
Next i

Application.Cursor = xlDefault
' gives control of the statusbar back to the programme
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

For the same I have used a userform with a label but it executes only before or after the macro execute

Private Sub UserForm_Activate()
 Call ShowProgressBarWithoutPercentage
End Sub

Sub ShowProgressBarWithoutPercentage()
Dim Percent As Integer
Dim PercentComplete As Single
Dim MaxRow, MaxCol As Integer
Dim iRow, iCol As Integer
MaxRow = 500
MaxCol = 500
Percent = 0
'Initially Set the width of the Label as Zero
UserForm1.Label1.Width = 0
For iRow = 1 To MaxRow
    For iCol = 1 To MaxCol
        Worksheets("Sheet1").Cells(iRow, iCol).Value = iRow * iCol

    Next
    PercentComplete = iRow / MaxRow
    UserForm1.Label1.Width = PercentComplete * UserForm1.Width

Next
Unload UserForm1
End Sub

Can someone show a method to show the progressbar when macro is running in background?

Upvotes: 1

Views: 27880

Answers (2)

Alan Perry
Alan Perry

Reputation: 1

This might have been answered elsewhere... but I believe it answers OP's question without any extra forms, labels, progress bar controls or interfering with any sheets contents.

Taken from my code for the Advent of Code 2015, Day 4 , Part 1 ---

The Worksheets Status Bar displays the progress as a bar graph,

Sub Test1()
' Advent of Code 2015 Day 4, Part 1

Dim counter As LongLong
Dim aString As String
Dim Status As String
Dim BarMultiplier As Long
Dim BarProgress As Long

Const MaxCounter = 4294967#  '4294967296#
Const BarNoSegments = 30


Debug.Print Now()    ' Prints date and time to Immediate Window

BarMultiplier = MaxCounter / BarNoSegments

For counter = 0 To MaxCounter
  
' This part updates the Excel Status bar every 1000 cycles, otherwise it looks like it hangs (Excel can grey and the circle of waiting appears)
' But do it tooo often it slows the calculation with screen updates
  
    If (counter Mod 1000) = 0 Then
        BarProgress = Int((counter + 1) / BarMultiplier)
        
        Status = ""
        
        'Status = "Counter " & counter & ", Progress " & (Int((counter + 1) / MaxCounter * 100) & "% ") ' Used if you want to see the counters during the run
        
        Status = Status & "[" & String(BarProgress, "-")
        
        Status = Status & String(BarNoSegments - BarProgress, "_") & "]"
         
        Application.StatusBar = Status
        
        DoEvents    ' Allows screen to update
        
    End If
    
    
    ' Code using the variable counter Here!
    
    
    
    Next counter

    Application.StatusBar = "Done " & Now()

   End Sub

Upvotes: -1

Jamie Bull
Jamie Bull

Reputation: 13539

The problem could be your Application.ScreenUpdating = False. You could update the screen periodically, but that might negate the benefits of having it set to False in the first place. The status bar still updates though, so you could write something like the following to the status bar.

0%  |
10% ||||||

And update that as the macro runs.

25%  ||||||||||||||
...
50%  ||||||||||||||||||||||||||||
...
100% ||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Here's an example:

Sub StatusBarPercent(Percent As Double)
    Dim i As Long
    Dim Status As String
    Percent = Percent * 100
    Status = "Formatting Report...  " & Percent & "% "
    For i = 0 To Percent
        Status = Status & "|"
    Next
    Application.StatusBar = Status
End Sub

Upvotes: 3

Related Questions