Excel: Progress Bar Indicator with VBA

There was a request for an Excel Progress bar with percentage indicator. While I personally don’t understand the need for something like that, I am happy to oblige.

Lets say we have this fictitious scenario. We have a spreadsheet with 65536 numbers in it. I want to go through each number, and in the cell next to it, put an ‘X’ if the number is a prime number. There will be a small form that pops up with a progress bar and percentage indicator while the macro is processing. I am purposely using a inefficient function for determining Prime Numbers so we can have a noticeable delay in the processing in order to demonstrate the Progress Bar indicator.

To start, create a new Excel worksheet. In Cell A1, put in the value of 1. Select all of Column A, go up to “Edit”, “Fill”, and select “Series”. This will fill out all of column a with the numbers 1 – 65536.



I will now create the progress bar itself on a user form. First, I go into the Excel Macro Editor, choose this workbook, go to the “Insert” menu at the top of the screen, and select UserForm. When the component toolbox opens up, I right mouse click on it and choose “Additional Controls”. (Alternatively, I could go up to the “Tools”/”Additional Controls” menu) From the list of components, I select “Microsoft Progress Bar Control 6.0 (SP4)”. I then draw a form with the progress bar and 1 text label, as illustrated below. One thing to be sure of is to set the UserForm1 property of “ShowModal” to false; otherwise the progress bar will display and sit without processing anything else in the script.



And below is the code for the Workbook.

Option Explicit

'Very basic function for finding is a number is prime. Just go through all numbers
'From 2 to 1/2 of the number and divide. If the number is divisible by any of those
'numbers, then it is not a prime number. Check for divisibility by 2 or 3 first to
'Try to speed things up just a little
Function isPrime(n As Long) As Boolean
Dim counter As Long

'ignore the values of 1, 2, and 3 and answer appropriately
If (n = 1) Then Exit Function

If (n = 2) Or (n = 3) Then
isPrime = True
Exit Function
End If

'If number is divisible by 2 or 3, it is not prime
If (n Mod 2 = 0) Then Exit Function
If (n Mod 3 = 0) Then Exit Function

'Go through remaining numbers, if divisible by any of them, it is
'not prime
For counter = 5 To (n / 2)
If (n Mod counter = 0) Then Exit Function
Next

'If we got this far, it must be a prime number
isPrime = True
End Function

Sub ProgressBarDemonstration()
'Used to track the last row in the worksheet
Dim LastRow, counter, percentage As Long

'Get the number of rows in this worksheet by using Excels internal countA function
'I use this instead of looking for blank cells because this has shown itself to be more accurate
If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If

'If there are no rows, exit out of the function
If LastRow = 0 Then
MsgBox "There are no rows in this worksheet", vbOKOnly
Exit Sub
End If

'Show the user the progress bar
UserForm1.Show

'On error, use the error handler to hide the progress bar
On Error GoTo err_handle

'For all numbers in the worksheet, find out if it is a prime number. If it is,
'put an X next to the number
For counter = 1 To LastRow
If isPrime(Cells(counter, 1).Value) Then
Cells(counter, 2).Value = "X"
End If

'Caluculate the percentage of completion
percentage = (counter / LastRow) * 100

'Update the progress bar and the text indicator
UserForm1.ProgressBar1.Value = percentage
UserForm1.lblPercent = Str(percentage) & "%"

'Do any background events so we don't leave the system unresponsive
DoEvents
Next

'Hide the progress bar when completed
UserForm1.Hide

Exit Sub
'Hide the progress bar in the event of an error
err_handle:
UserForm1.Hide
End Sub

When the code is run, a progress bar will display showing the functions progress.