VBA display progress (e.g. in status bar) for huge iteration

[gifariz]

I have large data up to 1 million rows. My purpose is to show the iteration progress of VBA calculation, e.g. in Application.StatusBar.
I tried like this below, to update status bar only when progress is increased 1%. But the status bar is still updated in every single iteration, that is 1/1000000 or 0.0001% progress increment (heavily slowing the macro), I don't know why.

Private Sub Show_Progress()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    Dim i as long, Ln As Long
    Dim prog As Long, prog2 As Long

    Ln = 100000

    For i = 0 To Ln - 1
        prog = Int((i + 1) / Ln * 100)
        If prog > prog2 Then
            Application.StatusBar = "Calculating data (" & i + 1 & "/" & Ln & ")"
        Else
            prog2 = prog
        End If
        'Actually do something, but do nothing for example
    Next i
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

The turning off calculation, screen updating, and disabling events not really helpful.

Any solution please? Thank you

 

[Akuini]

This will update the status bar in every 10K iteration:

Sub gifariz()

n = 1000000
m = 10000
For i = 1 To n
    
    If i Mod m = 0 Then
        Application.StatusBar = "Progress: " & 100 * i / n & "%"
    End If

Next

End Sub

 

[gifariz]

This will update the status bar in every 10K iteration:

Sub gifariz()

n = 1000000
m = 10000
For i = 1 To n
   
    If i Mod m = 0 Then
        Application.StatusBar = "Progress: " & 100 * i / n & "%"
    End If

Next

End Sub

Works like a charm, thank you!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.