Excel VBA StatusBar

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 barStatus BarAs the name implies, the status bar displays the current status in the bottom right corner of Excel; it is a customizable bar that can be customized to meet the needs of the user.read more showing the percentage of work completed so far, like the one below.

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.

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

Example to Create StatusBar using VBA

Follow the below steps to create a status bar.

Step 1: First, define the VBA variableDefine The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more 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.

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.

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.

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.

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.

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

Step 7: We need to include the For Next loop in VBAFor Next Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more to calculate the percentage of the completed Macro. Then, define a variable to start the Macro.

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 the “Present Status.” So, for the variable “PresentStatus,” we need to apply the formula below.

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.

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

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

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.

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

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

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

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.

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.

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.

Below is the code for you.

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

  • Barcode in ExcelListObjects in VBAVBA Find NextCDEC in VBA