Home » Blogs » Nick's blog

10. VBA Tips - Update Statusbar

Nick's picture
Submitted by Nick on 8 April, 2009 - 14:21


This VBA tip is for people designing systems in Excel:

  • You've written some code that takes a while to run
  • You don't want your users to think nothing is happening
  • This tip shows you how to update them on the code's progress

The example we will use is that of changing a cell's value from 1 to 1000.

 

Here's a screen shot of our data in Excel, and our VBA code:

Update-Statusbar

Explanation:

  1. At the start of the code, we tell users we are "processing"
  2. We then enter a loop, and at each iteration we update where we are
  3. At the end of the code, we say that we've finished
  4. The updates appear on the statusbar which is under "sheet1" tab

Download sheet to practise how to Update Statusbar in VBA

Training Video on how to Update Statusbar in VBA:

AttachmentSize
update-statusbar.xls31 KB
»

THE CODE IN MY PC RUN VERY SLOWLY

Submitted by VG-EXCEL-GEST on 25 January, 2011 - 08:39.

SAMPLE TEST FILE ON MY PC. WHY? THE CODE IN MY PC RUN VERY SLOWLY?

»
Nick's picture

slow code

Submitted by Nick on 25 January, 2011 - 09:03.

most likely, you have other sheets open, and calculation on automatic

turn to manual

»

Updating the statusbar

Submitted by josalkeld on 20 January, 2010 - 11:45.

I have found that some times the sheer number of iterations can be a distraction if the status bar updates each time.

 
Try ..
 
Sub UpdateStatusBar()
Dim i as long
Dim LB as long
Dim UB as long
Dim intUpDateNumber as long
 
for i = LB to UB
 intUpDateNumber = INT((UB - LB) /100)
 if (i - LB) mod intUpDateNumber = 0 then
    Application.StatusBar = "Iteration : " & Format(i,"#,##0") & " of " & Format(UB-LB+1,"#,##0")
 end if
 '... Do Stuff
 
next i
Application.statusBar = "Iteration Completed."
'... Do More Stuff
 
' ... reset statusbar before exit
Application.StatusBar = false
 
End Sub
»