Instantly share code, notes, and snippets.

Embed
What would you like to do?
This macro demonstrates a "progress bar" that simply updates Excel's status bar -- simple but effective as hell!
Option Explicit
Public Sub WriteFakeLastNames()
Dim wksNames As Worksheet
Dim lngLastRow As Long, lngIdx As Long
Dim strOldName As String, strNewName As String
'Set references up-front
Set wksNames = ThisWorkbook.Worksheets("names")
lngLastRow = 1001
'Set the context, everything will take place on "names" sheet
With wksNames
'Enter loop and start updating the application status bar
For lngIdx = 2 To lngLastRow
'Create the fake name by appending "Smith" to each old name
strOldName = .Cells(lngIdx, 1)
strNewName = strOldName & "Smith"
'Write the fake name to column B
.Cells(lngIdx, 2) = strNewName
'This is the exciting part, where we update the status bar!
Application.StatusBar = "Processing row " & lngIdx & _
" of " & lngLastRow & "... " & _
Format(lngIdx / lngLastRow, "0%") & _
" complete"
Next lngIdx
'Reset the status bar now that we're done
Application.StatusBar = False
End With
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment