VBA Status Bar

Excel VBA Status Bar

Status Bar is the property of a vba which is used to display the status of the code finished or completed at the time of execution, it is displayed at the left hand side corner of the worksheet when a macro is executed and the status is shown in percentage to the user.

When the macro is running behind it is a frustrating thing to wait without knowing how long it is going to take. If ur at the stage where the code is running, you can at least calculate the time it is going to take. So, the idea is to have a status bar showing the percentage of work has been completed so far like the below one. This would be great, isn’t it? Ok, in this article we will show you how to write VBA code to enable the excel status bar to show the progress like the above one.

What is Application.StatusBar?

Application.StatusBar is the property that we can use in macro coding to show the status when the macro is running behind the scene.

This is not as beautiful as our “VBA Progress Bar” but good enough to know the status of the macro project.

Example of VBA Status Bar

Let’s take an example of the VBA Status Bar.

Follow the below steps to create a status bar in VBA.

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 by 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 need to define the variable to hold number 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

This 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 current status and percentage completed when the macro is running.

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 we need to enable the status bar, to enable status bar use 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 = "[" & Space(NumOfBars) & "]"

End Sub

What this will do it will add the bracket ([) and add 45 spaces characters before ends the text with closing bracket (]).

Execute the code and we could see the below in the excel VBA status bar.

Output:

Step 7: Now we need to include the For Next loop in VBA to calculate the percentage of the macro that has been completed. 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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

  Next k

End Sub

Step 8: Inside the loop, we need to calculate what the “Present Status” is. So for the variable “PresentStatus”, we need to apply the formula as 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 = "[" & Space(NumOfBars) & "]"

  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 as a result.

Step 9: Now we need to calculate what the “Percentage Completion” is, so we can apply the formula as 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 = "[" & Space(NumOfBars) & "]"

  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 because whatever the decimal places we need to round to nearest zero value, so ROUND with zero as the argument has been used here.

Step 10: We have already inserted the starting bracket and end bracket to the status bar, now we need to insert 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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

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

  Application.StatusBar = "[" & String(PresentStatus, "|") &
  Space(NumOfBars - PresentStatus) & _"] " & PercetageCompleted & "% Complete"

  Next k

End Sub

In the above code, we have inserted opening bracket “[“ and to show the progress of the macro we have inserted straight line (|) by using STRING function, when the loop is running it will take the “PresentStatus” and those many straight lines will be inserted in the status bar.

Code:

Application.StatusBar = "[" & String(PresentStatus, "|")

Next, we need to add space characters between one straight line to the other, so this will be calculated by using “NumOfBars” minus “PresentStatus”.

Code:

Application.StatusBar = "[" & String(PresentStatus, "|") & 
Space(NumOfBars - PresentStatus)

Then we close out the bracket “]”. Next, we have combined the “PercentageCompleted” variable value while the loop is running with the word in front of it as “% Completed”.

Code:

Application.StatusBar = "[" & String(PresentStatus, "|") & 
Space(NumOfBars - PresentStatus)& _"] " & PercetageCompleted & "% Complete"

When the code is running 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 = "[" & Space(NumOfBars) & "]"

 Dim k As Long
 For k = 1 To LR

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

 Application.StatusBar = "[" & String(PresentStatus, "|") &
 Space(NumOfBars - PresentStatus) & _ "] " & PercetageCompleted & "% Complete"
 DoEvents

 Next k

End Sub

Step 11: After adding “Do Events” we can write the codes that need to be executed here.

For example, I want to insert serial numbers to the cells, so I will write code as 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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

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

  Application.StatusBar = "[" & String(PresentStatus, "|") 
  & Space(NumOfBars - PresentStatus) & _"] " & 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 near the last used row in the worksheet then we need to make the status bar as 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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

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

  Application.StatusBar = "[" & String(PresentStatus, "|") & 
  Space(NumOfBars - PresentStatus) & _"] " & 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

Ok, we are done with coding, as you execute the code here you can see the excel VBA 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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

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

  Application.StatusBar = "[" & String(PresentStatus, "|") &
  Space(NumOfBars - PresentStatus) & _"] " & 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

Things to Remember About VBA Status Bar

• We can add only the tasks that need to be done within the loop.
• You can add the tasks that need to do after adding the “Do Events” procedure.

Recommended Articles

This has been a guide to VBA Status Bar. Here we discuss how to enable the status bar to show the progress using VBA code in excel along with a practical example and a downloadable template. Below you can find some useful excel VBA articles –

• ListObjects in VBA
• Excel VBA Find Next
• CDEC in VBA
• SendKeys in VBA
• On Error Goto 0 in VBA
• On Error Resume Next in VBA