Excel Progress Bar

Routine to provide Macro Progress Bar in Excel. Original idea from Andy Pope MVPS

Declare statement only needed for demonstration of code

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Use the following code with your own process embedded as appropriate

Sub TestProg()

Dim intMax As Integer ‘Maximum progress

Dim intProg As Integer ‘Iterative progress

intMax = 50

For intProg = 1 To intMax

ProgBar (intProg / intMax) * 100

' Your code here

Sleep 250

Next

Application.StatusBar = “”

End Sub

Insert this procedure in your main module or a separate module depending on what standards you are using

Sub ProgBar(intPC As Single)

Dim intLen As Integer ‘Number of blocks in progress bar

Dim intProg As Integer ‘Number of blocks to shade

Dim strProg As String ‘String of characters in Status Bar

intLen = 20

intProg = intLen * (intPC / 100)

strProg = String(intProg, Chr(149)) & String(intLen - intProg, Chr(111))

Application.StatusBar = "Processing " & strProg

End Sub