【ExcelVBA】残りの処理時間と進捗をステータスバーに表示する方法

Sub 残り時間計測()

    Dim time1 As Double
    Dim i As Long
    Dim pre_i As Long
    Dim j As Long
    Dim remaining_time As Double
    Dim first_num As Long
    Dim last_num As Long
           
    first_num = 1
    last_num = 50000
    pre_i = first_num
    time1 = Timer

'******************ここから処理*******************
    For i = first_num To last_num
            
        '********テスト用ループ処理***********
        For j = 1 To 1000000

        Next j
        '********テスト用ループ処理***********
     
    If (Timer - time1) > 1 Then '前回から1秒以上経過している場合に処理を実行
    
        remaining_time = (Timer - time1) * (last_num - pre_i) / (i - pre_i) '残り時間計算
        pre_i = i '現在のインデックスiを記憶
        time1 = Timer '現在の経過時間を取得k

        Application.StatusBar = _
        "進捗： " & Int(100 * i / last_num) & "% " & _
        "残り時間：" & _
        Int(remaining_time / 3600) & "時間" & _
        Int(remaining_time / 60) Mod 60 & "分" & _
        Int(remaining_time) Mod 60 & "秒"
        DoEvents

    End If
    
    Next i

'******************ここまで処理*******************

    MsgBox "終了です"

End Sub