How to Display Progress on the Status Bar of Your Excel

Not knowing how long a program will run makes us restless. The program might have hung, or might finish in five minutes – or maybe in two hours. This article looks at keeping the anxious user informed in real-time via the Status Bar.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The exercise will look like this, using the example file found here.

The Process

The program will extract the data, where the Supplier has a country, to an array.

Once the data has been extracted, the program will write the array to the results sheet.

In each case the Status Bar will show the progress.

The Code

In this code, every time the nominator (row number) divided by 2000 = 0.00, the program reports progress.

Modify the denominator of 2000 as required.

Insert a VBA module and copy the following code into the code window:

Option Explicit
Dim arrSupplier(70000, 3) ‘a 2D array of 70,000 rows and three columns
Dim eRow As Long
Dim nProgress As Integer
Dim cRow As Long
Dim nMarker As Single
Dim nArrayRows As Long
Dim n As Long
Dim i As Long

Sub Main()
    Call LoadArray
    Call WriteResults
    MsgBox "The run is complete. The Status Bar will now be cleared."
    Application.StatusBar = False
    Application.Screenupdating = True
End Sub

Sub LoadArray()
    Application.ScreenUpdating = False 'prevent the screen from flickering
    Erase arrSupplier() 'ensure no residuals remain in the array
    Application.StatusBar = False 'clear the Status Bar
    Range("A3").Select
    Selection.End(xlDown).Select 'Find the last row, as denominator
    eRow = ActiveCell.Row
    n = -1
    Range("A3").Select
    Do While ActiveCell > "" 'Loop while data lasts
        If ActiveCell.Offset(0, 1) > "" Then 'If there is a country, record the supplier
            n = n + 1
            arrSupplier(n, 0) = ActiveCell
            arrSupplier(n, 1) = ActiveCell.Offset(0, 1)
            arrSupplier(n, 2) = ActiveCell.Offset(0, 2)
        End If
        cRow = ActiveCell.Row
        nMarker = cRow / 2000 'seeking zero
        If cRow > 0 And nMarker - Int(nMarker) = 0 Then 'same result as using a 'Mod' operator
            nProgress = (cRow / eRow) * 100
            Application.StatusBar = "Writing data to array " & nProgress & "% complete"
            DoEvents
        End If
        Range("A" & ActiveCell.Row + 1).Select
    Loop
    nProgress = (cRow / eRow) * 100 'update the results, after the last marker
    Application.StatusBar = "Printing results " & nProgress & "% complete"
    nArrayRows = n
End Sub

Sub WriteResults()
    Sheets("Results").Activate
    Range("A3:C100000").ClearContents 'clear the results area
    Range("A3").Select

    For i = 0 To n 'loop through the array…
        If arrSupplier(i, 0) = "" Then Exit For '…until it is empty
        ActiveCell = arrSupplier(i, 0)
        ActiveCell.Offset(0, 1) = arrSupplier(i, 1)
        ActiveCell.Offset(0, 2) = arrSupplier(i, 2)
        cRow = ActiveCell.Row
        nMarker = cRow / 2000
        If cRow > 0 And nMarker - Int(nMarker) = 0 Then
            nProgress = (cRow / nArrayRows) * 100
            Application.StatusBar = "Printing results " & nProgress & "% complete"
            DoEvents
        End If
        Range("A" & ActiveCell.Row + 1).Select
    Next i
    nProgress = (cRow / nArrayRows) * 100 'update the results
    Application.StatusBar = "Printing results " & nProgress & "% complete"
End Sub

Add a button to start sub procedure “Main”

The Result

The Excel Status Bar will display progress. If you have a very fast computer, you might need to double or triple the amount of data.

Recovering corrupted workbooks

As Excel has become more sophisticated, it does tend to crash from time to time for a variety of reasons, from background printer margin settings to faulty Add-ins or general Office Suite damage. When it happens, it’s too late to take preventative measures; the work done since the last save might be irrevocably lost, unless you have an Excel repair tool to fix the corrupted workbook.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com