Skip to content

Instantly share code, notes, and snippets.

@danwagnerco
Created February 23, 2016 05:41
Show Gist options
  • Save danwagnerco/72ba89a0171ab47e0182 to your computer and use it in GitHub Desktop.
Save danwagnerco/72ba89a0171ab47e0182 to your computer and use it in GitHub Desktop.
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