VBA Progress update in Status bar stops updating at exactly row 327 every time-CodePudding

I've created for loops in vba which update progress in status bar by the value of loop integer. There are 2 loops; progress in one loop works perfectly fine while in other it gets stuck though both are almost replicas. Tried different files, rebooting, closing excel etc, every time it stucks at row 327. idk why, row 327 is not special, in different files too it's always row 327. It's also not working so fast that it could be left behind. loop goes through thousands of rows and with time gets pretty slow, so it moves like one row in one second; that shouldn't be too fast to update status bar simultaneously. More Strange behavior is that when it goes to next workbook after completing one file, first loop still updates the progress bar again, but second loop again gets stuck at row 327.

I couldn't attach full code here, it's more than 800 lines, even this module is 320 lines so I attached a sample here.

(overall code works perfect, it's only the progress bar which gets stuck)

for i = 2 to lastrow
Application.StatusBar = Round((i * 100) / lastrow, 0) & "% row " & i & " of " & lastrow

    if i mod 2 = 0 then
       'my code here
    else
       'my code here
    end if
next 

for x = 2 to lastrow
Application.StatusBar = Round((x * 100) / lastrow, 0) & "% row " & x & " of " & lastrow

    if x mod 2 = 0 then
       'my code here
    else
       'my code here
    end if
next

CodePudding user response：

I strongly assume that you are a victim of a numerical overflow. 327 * 100 = 32700, that value fits into a 16-bit integer. 328 * 100 = 32800 and doesn't fit into an integer. In my tests, I got an overflow runtime error with your calculation formula.

What I therefore assume is that

You declared i and x as Integer. Best idea is to forget about that in VBA that data type exists, use Long instead, see Integer Vs Long Confusion
You somewhere have an error handling routine that hides the overflow error that should pop up.

Declaring your variables as Long should do the trick:

Option Explicit

Const lastrow = 500
Sub test1()
    Dim x As Integer
    For x = 1 To lastrow
        ' Throws runtime error when x >= 328
        Application.StatusBar = Round((x * 100) / lastrow, 0) & "% row " & x & " of " & lastrow
    Next
End Sub

Sub test2()
    Dim x As Long
    For x = 1 To lastrow
        ' No runtime error
        Application.StatusBar = Round((x * 100) / lastrow, 0) & "% row " & x & " of " & lastrow
    Next
End Sub