FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA StatusBar

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel 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.

VBA Status Bar Example 1.11

What is Application.StatusBar?

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.

VBA-Status-Bar

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA StatusBar (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example to Create StatusBar using VBA

You can download this VBA Status Bar Excel Template here – VBA Status Bar Excel Template

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
VBA Status Bar Example 1

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
Example 1.1

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
VBA Status Bar Example 1.2

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
Example 1.3

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
VBA Status Bar Example 1.4

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 = "[" & Space(NumOfBars) & "]"

End Sub
 Example 1.5

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

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

Output:

VBA Status Bar Example 1.6

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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

  Next k

End Sub
VBA Status Bar Example 1.7.0

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 = "[" & Space(NumOfBars) & "]"

  Dim k As Long
  For k = 1 To LR

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

  Next k

End Sub
 Example 1.8.0

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 = "[" & 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
 Example 1.9.0

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 = "[" & 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 the opening bracket “[“and to show the progress of the Macro, we have inserted a straight line (|) by using the STRING function. When the loop runs, it will take the “PresentStatus.” It will insert those many straight lines in the status bar.

Code:

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

Next, we need to add space characters between one straight line and the other, which will calculate 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 runs with the word in front of it as “% Completed.”

Code:

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

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

Output:

VBA Status Bar Example 1.10

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

  • 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.

This article has been a guide to VBA StatusBar. Here, we discuss enabling the status bar to show progress using VBA code in Excel, a practical example, and a downloadable template. Below you can find some useful Excel VBA articles: –