CODEWITHSUNDEEP
vbaexcel
Wei Hau
Wei Hau

Reputation: 1

Updating Excel Status Bar in VBA Loop

I have a macro code to perform trimming for data. Below is my code:

Sub StatusBarExample()
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Trimming..."
Dim x As Long
x = 1
Do While Cells(x, 1) <> ""
Cells(x, 2).Select
ActiveCell.FormulaR1C1 = "=trim(RC[-1])"
x = x + 1
Loop
Application.Wait Now + TimeValue("00:00:02")
Application.StatusBar = False
End Sub

The number of data always changes, which is why I use the Do While loop. With this code, I only can see a message "Trimming" without knowing the progress in percentage. Can anyone help me to improve this code?

Upvotes: 0

Views: 8295

Answers (1)

Kyle
Kyle

Reputation: 2545

I would take this approach. I like a for loop better, and this will show you the % complete in the status bar.

Sub StatusBarExample()
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Trimming..."
Dim x As Long
Dim lRow as Long
Dim prePct as Single, newPct as Single

newPct = 0
prePct = 0
With ThisWorkbook.Sheets("Sheet Name Goes Here")
   lRow = .Range("A" & .Rows.Count).End(xlUp).Row

   For x = 1 to lRow
      .Cells(x,2).FormulaR1C1 = "=trim(RC[-1])"
      newPct = x/lRow

      If newPct > prePct + .01
         DoEvents
         prePct = newPct
      End If

      Application.StatusBar = "Trimming..." & Format(x/lRow,"0.00%") & " Complete"
   Next x
Application.StatusBar = False
Application.ScreeUpdating = True
End Sub

Thank you for the suggested edits. I made a couple changes. In the line If newPct > prePct + .01, you may need to change the .01 to something else depending on the amount of data.

Upvotes: 2

Related Questions