VBA StatusBar

StatusBar is the property of a VBA one may use to display the status of the completed code at the time of execution. For example, it displays at the left-hand side corner of the worksheet when a Macro executes. The status shows in percentage to the user.

When the Macro is running behind, it is frustrating to wait without knowing how long it will take. But, if you are at the stage where the code is running, you can at least calculate the time it will take. So, the idea is to have a status bar showing the percentage of work completed so far, like the one below.

The Application.StatusBar is the property we can use in macro coding to show the status when the Macro is running behind the scenes.

It is not as beautiful as our "VBA Progress Bar" but good enough to know the status of the Macro project.

Follow the below steps to create a status bar.

Step 1: First, define the VBA variable to find the last used row in the worksheet.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long

End Sub

Step 2: Find the last used row using the below code.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row

End Sub

Step 3: Next, we need to define the variable to hold the number of bars to be displayed.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer

End Sub

It will hold how many bars are allowed to show in the status bar.

Step 4: For this variable, store the limit of the bar as 45.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

End Sub

Step 5: Define two more variables to hold the current status and percentage completed when the Macro runs.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

End Sub

Step 6: Now, use the code below to enable the status bar.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

End Sub

What this will do is add the bracket ().

Execute the code. See the below in the Excel VBA status bar.

Output:

Step 7: We need to include the For Next loop in VBA to calculate the percentage of the completed Macro. Then, define a variable to start the Macro.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  Next k

End Sub

Step 8: Inside the loop, we need to calculate the "Present Status." So, for the variable "PresentStatus," we need to apply the formula below.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)

  Next k

End Sub

We have used the INT function to get the integer value.

Step 9: We need to calculate the "Percentage Completion" to apply the formula shown below.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)
  PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

  Next k

End Sub

In this case, we have used the ROUND function in Excel because we need to round to the nearest zero value, whatever the decimal places. So, ROUND with zero as the argument used here.

Step 10: We have already inserted the starting bracket and end bracket into the status bar, now we need to insert the updated result, and it can be done by using the below code.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)
  PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

  Application.StatusBar = " " & PercetageCompleted & "% Complete"

  Next k

End Sub

In the above code, we have inserted the opening bracket "." Next, we have combined the "PercentageCompleted" variable value while the loop runs with the word in front of it as "% Completed."

Code:

Application.StatusBar = " " & PercetageCompleted & "% Complete"

When the code runs, we allow the user to access the worksheet, so we need to add "Do Events."

Code:

Sub Status_Bar_Progress()

 Dim LR As Long
 LR = Cells(Rows.Count, 1).End(xlUp).Row
 Dim NumOfBars As Integer
 NumOfBars = 45

 Dim PresentStatus As Integer
 Dim PercetageCompleted As Integer

 Application.StatusBar = ""

 Dim k As Long
 For k = 1 To LR

 PresentStatus = Int((k / LR) * NumOfBars)
 PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

 Application.StatusBar = " " & PercetageCompleted & "% Complete"
 DoEvents

 Next k

End Sub

Step 11: After adding "Do Events," we can write the codes that need to execute here.

For example, we want to insert serial numbers into the cells, so we will write the code below.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)
  PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

  Application.StatusBar = " " & PercetageCompleted & "% Complete"

  DoEvents

  Cells(k, 1).Value = k
  'You can add your code here

  Next k

End Sub

Step 12: Before we come out of the loop, we need to add one more thing, i.e., If the loop is near the last used row in the worksheet, then we need to make the status bar normal.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row
  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)
  PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

  Application.StatusBar = " " & PercetageCompleted & "% Complete"

  DoEvents

  Cells(k, 1).Value = k
  'You can add your code here
  'You can Add your code here
  'You can Add your code here
  'You can add your code here
  'You can add your code here
  'You can add your code here

  If k = LR Then Application.StatusBar = False

  Next k

End Sub

We have completed the coding. As you execute the code, you can see the status bar updating its percentage completion status.

Output:

Below is the code for you.

Code:

Sub Status_Bar_Progress()

  Dim LR As Long
  LR = Cells(Rows.Count, 1).End(xlUp).Row

  Dim NumOfBars As Integer
  NumOfBars = 45

  Dim PresentStatus As Integer
  Dim PercetageCompleted As Integer

  Application.StatusBar = ""

  Dim k As Long
  For k = 1 To LR

  PresentStatus = Int((k / LR) * NumOfBars)
  PercetageCompleted = Round(PresentStatus / NumOfBars * 100, 0)

  Application.StatusBar = " " & PercetageCompleted & "% Complete"

  DoEvents

  Cells(k, 1).Value = k
  'You can add your code here
  'You can Add your code here
  'You can Add your code here
  'You can add your code here
  'You can add your code here
  'You can add your code here

  If k = LR Then Application.StatusBar = False
  Next k

End Sub

• We can add only the tasks we need to do within the loop.
• After adding the "Do Events" procedure, we can add the tasks you need to do.