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 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.
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 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.
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