VBA StatusBar

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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
Table of contents

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

Example to Create StatusBar using VBA

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 = ""

End Sub
Example 1.5

What this will do is add the 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 = ""

  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 = ""

  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 = ""

  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 = ""

  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:

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 = ""

  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

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: -