WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA StatusBar

VBA StatusBar

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA StatusBar

StatusBar 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 you are 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.

VBA Status Bar Example 1.11

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.

VBA Status Bar

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

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

Example 1.3

Step 5: Define two more variables to hold current status and percentage completed when the macro is running.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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, to enable the 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

 Example 1.5

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:

VBA Status Bar Example 1.6

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

VBA Status Bar Example 1.7.0

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

 Example 1.8.0

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

 Example 1.9.0

In this case, we have used the ROUND function in excel because whatever the decimal places, we need to round to the 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 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 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 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 that need to be done within the loop.
  • You can add the tasks that you need to do after adding the “Do Events” procedure.

Recommended Articles

This has been a guide to VBA StatusBar. Here we discuss how to enable the statusbar 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 –

  • Barcode in Excel
  • ListObjects in VBA
  • VBA Find Next
  • CDEC in VBA
9 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Status Bar Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More