Adding time elapsed to progress bar

[Waimea]

Hi, I have a userform called Progress which has a frame named Border, a label named Bar and another label called Text.

With this userform I would like to add time elapsed and possible time remaining?

In this thread time elapsed and time remaining is calculated, however, I can't modify it to work with my code since I don't understand the code.
https://www.mrexcel.com/forum/excel-questions/653806-progress-bar-countdown-timer-elapsed-time.html

Sub Progressbar()


Dim CurrentProgress As Double
Dim ProgressPercentage As Double
Dim BarWidth As Long


Call InitProgressBar


CurrentProgress = L / 1600
BarWidth = Progress.BorderColor.Width * CurrentProgress
ProgressPercentage = Round(CurrentProgress * 100, 0)


Progress.Bar.Width = BarWidth
Progress.Text.Caption = ProgressPercentage & "% Complete"


DoEvents


End Sub

Sub InitProgressBar()


With Progress
   .Bar.Width = 0
   .Text.Caption = "0% Complete"
   .Show vbModeless
End With


End Sub

 

[Roderick_E]

let's see your code please. Are you using a FOR loop?
Thanks

 

[Waimea]

Sub MyCode()
Dim L As Integer
Dim N As Integer


Dim CurrentProgress As Double
Dim ProgressPercentage As Double
Dim BarWidth As Long

Dim Time_Elapsed As Long
Dim Time_Start As Long

Time_Start = Now()


Call InitProgressBar


For L = 0 To 39
Sheets("Dash").ComboBox1.ListIndex = L


CurrentProgress = L / 1600
BarWidth = Progress.BorderColor.Width * CurrentProgress
ProgressPercentage = Round(CurrentProgress * 100, 0)

With Progress
.Bar.Width = BarWidth
.Text.Caption = ProgressPercentage & "% Complete"
.Text2.Caption = "Time elapsed: " & Time_Elapsed???
End With



For N = 0 To 39
Sheets("Dash").ComboBox2.ListIndex = N


Next N
Next L


End Sub

Thanks for your reply! I am using two FOR loops but I am only calculating the progress of the outer loop?

I have added a label called Text2 where I want to display the time elapsed.

 

[John_w]

Change Time_Start to As Date and include this:

    Dim elapsedTime As Date, remainingTime As Date
    elapsedTime = Now - Time_Start
    
    If elapsedTime > 0 And ProgressPercentage > 0 Then
        remainingTime = (100 / ProgressPercentage * elapsedTime) - elapsedTime
        With Progress
            .Bar.Width = BarWidth
            .Text.Caption = ProgressPercentage & "% Complete"
            .text2.Caption = "Elapsed = " & Int(elapsedTime * 86400) & " secs, Remaining = " & Int(remainingTime * 86400) & " secs"
        End With
    End If

 

[Waimea]

Thank you for your reply John_w! Your code works great and I understand it.

 

[John_w]

Glad it works! But shouldn't this line:

CurrentProgress = L / 1600

be

CurrentProgress = (L + 1) / 40

? Because your progress bar is updated inside the For L loop, which ranges from 0 to 39.

To update the progress bar inside the nested For N loop, move all the calculations and ProgressBar code inside the For N loop and change the above line to:

CurrentProgress = (L + 1) * (N + 1) / 1600

 

[Waimea]

Thank you for your reply! I'll try it tomorrow!

How would I create a function that I call with this code? That I would pass the values of N and L to?

 

[Waimea]

I don't understand all of the code, if you have the time could you comment your code?

 

[John_w]

The calculations are based on the ProgressPercentage. Here is my code with comments, which I hope make sense:

    Dim elapsedTime As Date, remainingTime As Date
    
    'Elapsed time = current time - start time
    elapsedTime = Now - Time_Start
    
    'Calculate remaining time only if ProgressPercentage is greater than zero to avoid divide by zero error
    
    If elapsedTime > 0 And ProgressPercentage > 0 Then
        
        'Total estimated time is the elapsed time as a proportion of 100% of the progress so far: Total estimated time = 100% of progress / current progress% * elapsed time
        'Therefore remaining time = Total estimated time - elapsed time

        remainingTime = (100 / ProgressPercentage * elapsedTime) - elapsedTime
        
        With Progress
            .Bar.Width = BarWidth
            .Text.Caption = ProgressPercentage & "% Complete"
            
            'Convert Excel date-times to seconds by multiplying by the number of seconds in 1 day (24 * 60 * 60 = 86,400)
            'See http://www.cpearson.com/excel/datetime.htm
        
            .Text2.Caption = "Elapsed = " & Int(elapsedTime * 86400) & " secs, Remaining = " & Int(remainingTime * 86400) & " secs"
        End With
    End If

The total estimated time, and therefore the remaining time, can be simplified by factoring out the 100s:

        remainingTime = elapsedTime / CurrentProgress - elapsedTime

How would I create a function that I call with this code? That I would pass the values of N and L to?

The parameters needed to implement ProgressBar as a procedure without global variables (your L and Time_Start variables) would be the start time, the current L count and the maximum L count. Something like this, together with example calling code:

Public Sub Show_ProgressBar2()

    Dim startTime As Date, L As Long
    
    startTime = Now
    InitProgressBar
    
    For L = 1 To 1600
        ProgressBar2 startTime, L, 1600
        'Other code here to do main processing whilst progress bar updates
    Next
    
End Sub

Private Sub InitProgressBar()

    With Progress
       .Bar.Width = 0
       .Text.Caption = "0% Complete"
       .Show vbModeless
    End With

End Sub

Private Sub ProgressBar2(startTime As Date, currentCount As Long, maximumCount As Long)

    Dim CurrentProgress As Double
    Dim ProgressPercentage As Long
    Dim BarWidth As Long
    Dim elapsedTime As Date, remainingTime As Date
        
    CurrentProgress = currentCount / maximumCount
    BarWidth = Progress.Width * CurrentProgress
    ProgressPercentage = Round(CurrentProgress * 100, 0)
    elapsedTime = Now - startTime
    
    'Calculate remaining time only if ProgressPercentage is greater than zero to avoid divide by zero error
    
    If elapsedTime > 0 And ProgressPercentage > 0 Then
        
        'Total estimated time is the elapsed time as a proportion of the progress so far: Total estimated time = elapsed time / current progress
        'Therefore remaining time = Total estimated time - elapsed time
        
        remainingTime = elapsedTime / CurrentProgress - elapsedTime
        With Progress
            .Bar.Width = BarWidth
            .Text.Caption = ProgressPercentage & "% Complete"

            'Convert Excel date-times to seconds by multiplying by the number of seconds in 1 day (24 * 60 * 60 = 86,400)
            'See http://www.cpearson.com/excel/datetime.htm

            .Text2.Caption = "Elapsed = " & Int(elapsedTime * 86400) & " secs, Remaining = " & Int(remainingTime * 86400) & " secs"
        End With
    End If
    
    DoEvents
    
End Sub

 

[Waimea]

Thank you for your reply and for taking the time to comment the code! I am going it try it at once!