Keeping End Users Informed with the Status

Another property of the Application bar that I use frequently is the StatusBar property. You can see the status bar in Figure 5.1. Look closely at the bottom-left corner. See the text that reads, "I am the StatusBar. Hear me roar." You may have known right where to look, but many other people don't pay much attention to the status bar. Keep that in mind as you decide what information to display there. If it is critical or important information, you need to present it to the end user using a louder method, such as a message box, because the status bar certainly does not roar.

That said, the status bar is an excellent way to display informational messages to keep the user apprised of what is going on. The great thing about the status bar is that it is nonintrusive to the user and easy to use for the developer. When you display a message in the status bar, the user is not interrupted by having to click an OK button. I prefer to use the status bar to show an update of progress in procedures that process a number of files, rows, or cells. Here's an example in Listing 5.2.

Figure 5.1

The status bar is a nice, subtle way to display information to an end user.

0 Microsoft Office Excel 7003 Hera

Chapter five Ejtaijiples.xls L|fn|Xj

1 if] Be 6g 1

¿ew fisert Format

Tools £ata ^indo^ ttelp - S

J -JO 1 sLOi? 4.1

« 4a A • 1 *> • 1 A z - 111 iWl P



• id - B I

U = = = it ' i™ I b - A -


1 * - 1


8 C

e 1 f 1 e ■



















» H \Sheetl/ £heet2 /shoet3 / the StatusBar. Hear rrae roar1.


Listing 5.2: Using the StatusBar property to Display Information

' This subroutine tests the impact of ' using StatusBar to display lots of ' frequent messages. Sub TimeStatusBar()

Dim dStart As Double

Dim dResult As Double

Dim bDisplayStatusBar As Boolean

' Remember original status bar setting bDisplayStatusBar = Application.DisplayStatusBar ' Turn on the status bar Application.DisplayStatusBar = True

' Baseline test - no status bar, every row ' To isolate how long it takes to ' perform mod statement on all rows dStart = Timer TestStatusBar 1, False dResult = Timer - dStart

MsgBox Format(dResu1t, "0.00") & " seconds.", vbOKOnly

' Time using StatusBar - every row dStart = Timer TestStatusBar 1, True dResult = Timer - dStart

MsgBox Format(dResu1t, "0.00") & " seconds.", vbOKOnly

' Time using StatusBar - every fifth row dStart = Timer TestStatusBar 5, True dResult = Timer - dStart

MsgBox Format(dResu1t, "0.00") & " seconds.", vbOKOnly

' Restore the status bar to its original setting Application.DisplayStatusBar = bDisplayStatusBar End Sub

' This subroutine displays a message to the status bar ' (if desired) for each row in a worksheet using the ' interval specified.

Private Sub TestStatusBar(nInterva1 As Integer, bUseStatusBar As Boolean) Dim IRow As Long Dim lLastRow As Long Dim ws As Worksheet

' Using the first worksheet in this workbook

' No changes will be made to the worksheet.

Set ws = ThisWorkbook.Worksheets(l)

' Every version since Excel 97 has had

ILastRow = ws.Rows.Count

For IRow = 1 To ILastRow

' Test to see if the current row ' is the interval specified. If IRow Mod nInterval = 0 Then If bUseStatusBar Then

Application.StatusBar = "Processing row: " & IRow & _ " of " & ILastRow & " rows." End If End If


Application.StatusBar = False

Set ws = Nothing

End Sub

The first order of business when using the status bar is to make sure it is displayed. Users can turn the status bar on/off on the View tab of the Options dialog box (from Microsoft Excel, Tools ^ Options) or by using the Status Bar item on the View menu. To be courteous and respectful of the user's preferences, you should remember the user's original setting and restore it when you're done. To remember the setting, assign the value given by the DisplayStatusBar property of the Application object to a Boolean variable. Then set the DisplayStatusBar to true so that the status bar is displayed.

Next, you see three similar blocks of statements. Each block performs a test using the status bar and times each test to determine the performance impact of using it. The test, TestStatusBar, consists of a simple loop of, presumably, 65,536 iterations. I had no specific reason for choosing the number of rows in a worksheet other than processing rows may be the type of occasion when you'd use the status bar to display progress information. TestStatusBar updates the message displayed in the status bar at the interval specified by the input parameter nInterval. In order to establish a baseline of how much time it takes to execute the Mod statement that is evaluated in the If.. .Then statement, I also included a parameter that specifies whether the status bar should be used or not.

Displaying text in the status bar is performed by assigning the text that you want displayed to the Application.StatusBar property as shown here:

Application.StatusBar = "Processing row:" & IRow & _ " of " & ILastRow & " rows."

The only thing you need to remember is that anything you put in the status bar stays there until you change it or close and reopen Excel. Once you're done with the status bar, the easiest way to clear it and instruct Excel to use it as it normally would is to include the following line:

Application.StatusBar = False

This method is used at the end of the TestStatusBar procedure.

WARNING Any text you display using Application.StatusBar stays there until you change it, unless you restart Excel. Thus, it is important to use Application.StatusBar = False somewhere in your program. It's even better if you devise an error-handling routine that ensures that this line is executed even in the event of a run-time error.

Finally, the last order of business is to restore the original DisplayStatusBar setting that was in place before you began. To restore the original setting, just assign the setting you remembered in the bDisplayStatusBar variable to the Application.DisplayStatusBar property.

Performance Implications of Using the Status Bar

When you run the code in Listing 5.2, it illustrates the impact of using the status bar. If you don't use the status bar, the TestStatusBar procedure runs in 0.02 seconds. If you do use it and update its text every iteration, the TestStatusBar procedure runs in 5.12 seconds. And if you use an update interval of every 5 iterations, the processing time is reduced to 1.03 seconds.

From this, you can conclude that although using the status bar is useful, it is prudent to choose an updating interval that doesn't impact performance very much and still provides useful information to the user. If you are reading text from a file for example, and you want to let the user know what row you are on, an interval of 10 or 20 is probably a good start. This has hardly any noticeable impact on performance and your process runs so fast that it is doubtful your users will notice any interval at all.

0 0

Post a comment