Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Simplifying Development

One of the most used time-saving tools in the Visual Basic Editor is the IntelliSense popup that appears after typing a period (.) after an object. This popup lists all the methods and properties that are defined in the interface for that type of object. Unfortunately, when we try to set properties or call methods in a worksheet or userform class, the IntelliSense list contains so many items that it's hard to find the properties and methods that we need to use. If we follow the recommendations for encapsulating our code, for example, we shouldn't be setting any of a userform's properties from outside the form; we should instead be exposing the form's functionality through our own properties and methods. When viewing the IntelliSense popup for a userform, it shows our properties and methods mixed in with those of the form. Defining and using our own interface for the form enables us to restrict the list of properties and methods to only those that we choose to expose.

A Progress Bar

Many applications include some form of progress indication to show the status of lengthy routines. It's likely that such an indication will be used in multiple places in our application and it makes sense to implement it as a common function that can be called from all our routines. If we have an object-oriented design, we would ideally like to treat it just like any other object, using something like the code in Listing 11-14.

Listing 11-14. Using a ProgressBar Class

Sub LongRoutine()
Dim pbProgBar As ProgressBar
Dim iCounter As Integer
Set pbProgBar = New ProgressBar
pbProgBar.Title = "Professional Excel Development"
pbProgBar.Text = "Preparing report, please wait..."
pbProgBar.Min = 0
pbProgBar.Max = 1000
pbProgBar.Progress = 0
pbProgBar.Show
For iCounter = 0 To 1000
pbProgBar.Progress = iCounter
Next
pbProgBar.Hide
End Sub

There is nothing in this code to suggest that the progress indication is a userform. The code is only saying that we want to display some type of progress indication to the user; the way in which it's presented is entirely encapsulated within the ProgressBar class and could just as easily be a userform, a message in the status bar or an audible prompt.

To help other developers that might use the ProgressBar class, it would be ideal if the IntelliSense list only showed the seven properties and methods (Title, Text, Min, Max, Progress, Show and Hide) that we should be using to control the progress bar. Unfortunately, if the ProgressBar was a userform class, the IntelliSense list would show our 7 items lost among the other 57 properties and methods of userforms.

As well as making it harder to pick out the correct properties and methods to use, exposing the normal userform properties makes it tempting for the consumer of the progress bar class to set some of the other properties of the form. At worst, that could break the way in which the progress bar works, or make the progress bar appear differently in different parts of the application. At best, it would make it much harder for us to modify the implementation of the progress bar itself; our new implementation may break the nonstandard way in which the progress bar class has been used, so we would have to check (and test) everywhere that it's referred to.

By using a custom interface, we can guarantee that all users of the progress bar class are only able to use the properties and methods that we define in that interface. Doing so removes the temptation to use the normal userform properties and makes it impossible for consumers of the class to use the progress bar form in non-standard ways. This enables us to totally separate the implementation of the progress indication from the use of the progress indication; as long as we keep the same interface, we can implement it as a userform or as a simple class module that just updates the status bar.

The IProgressBar Interface

As before, we define the interface to use for our progress bar form by creating a new class module, giving it the name IProgressBar and adding empty routines for each of the elements on the interface, as shown in Listing 11-15.

Listing 11-15. The IProgressBar Interface Class

'Set and get the title
Public Property Let Title(sNew As String)
End Property
Public Property Get Title() As String
End Property
'Set and get the descriptive text
Public Property Let Text(sNew As String)
End Property
Public Property Get Text() As String
End Property
'Set and get the minimum value for the bar
Public Property Let Min(dNew As Double)
End Property
Public Property Get Min() As Double
End Property
'Set and get the maximum value for the bar
Public Property Let Max(dNew As Double)
End Property
Public Property Get Max() As Double
End Property
'Set and get the progress point
Public Property Let Progress(dNew As Double)
End Property
Public Property Get Progress() As Double
End Property
'Show the progress bar
Public Sub Show()
End Sub
'Hide the progress bar
Public Sub Hide()
End Sub

The FProgressBar Form

The FProgressBar form implements the IProgressBar interface by displaying the progress indication on a userform. The progress bar is made up of two superimposed frames, each containing a label. The back frame and label is blue-on-white, and the front frame and label is white-on-blue. The progress measure controls the width of the front frame, to give the appearance of the progress bar shown in Figure 11-3.

Figure 11-3. A Simple Progress Bar Form

The complete FProgressBar form can be found on the CD, in the workbook \Concepts\Ch11Interfaces\Progress Bars.xls, but is reproduced in a simple form in Listing 11-16.

Listing 11-16. The FProgressBar Form Module Implementing the IProgressBar Interface

'
' Name:          FProgressBar
' Description:  Displays a modeless progress bar on the screen
' Author:        Stephen Bullen
Option Explicit
' Implement the IProgressBar interface
Implements IProgressBar
' Store the Min, Max and Progress values in module variables
Dim mdMin As Double
Dim mdMax As Double
Dim mdProgress As Double
Dim mdLastPerc As Double
' Initialize the form to show blank text
Private Sub UserForm_Initialize()
lblMessage.Caption = "
Me.Caption = "
End Sub
'Ignore clicking the [x] on the dialog
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub
' Let the calling routine set/get the caption of the form
Private Property Let IProgressBar_Title(RHS As String)
Me.Caption = RHS
End Property
Private Property Get IProgressBar_Title() As String
IProgressBar_Title = Me.Caption
End Property
' Let the calling routine set/get the descriptive text
Private Property Let IProgressBar_Text(RHS As String)
If RHS <> lblMessage.Caption Then
lblMessage.Caption = RHS
End If
End Property
Private Property Get IProgressBar_Text() As String
IProgressBar_Text = lblMessage.Caption
End Property
' Let the calling routine set/get the Minimum scale
Private Property Let IProgressBar_Min(RHS As Double)
mdMin = RHS
End Property
Private Property Get IProgressBar_Min() As Double
IProgressBar_Min = mdMin
End Property
' Let the calling routine set the Maximum scale
Private Property Let IProgressBar_Max(RHS As Double)
mdMax = RHS
End Property
Private Property Get IProgressBar_Max() As Double
IProgressBar_Max = mdMax
End Property
' Let the calling routine set the progress amount.
' Update the form to show the progress.
Private Property Let IProgressBar_Progress(RHS As Double)
Dim dPerc As Double
mdProgress = RHS
'Calculate the progress percentage
If mdMax = mdMin Then
dPerc = 0
Else
dPerc = Abs((RHS - mdMin) / (mdMax - mdMin))
End If
'Only update the form every 0.5% change
If Abs(dPerc - mdLastPerc) > 0.005 Then
mdLastPerc = dPerc
'Set the width of the inside frame,
'rounding to the pixel
fraInside.Width = Int(lblBack.Width * dPerc / _
0.75 + 1) * 0.75
'Set the captions for the blue-on-white and
'white-on-blue text
lblBack.Caption = Format(dPerc, "0%")
lblFront.Caption = Format(dPerc, "0%")
'Refresh the form if it's being shown
If Me.Visible Then
Me.Repaint
End If
End If
End Property
Private Property Get IProgressBar_Progress() As Double
IProgressBar_Progress = mdProgress
End Property
'Show the form modelessly
Private Sub IProgressBar_Show()
Me.Show vbModeless
End Sub
'Hide the form
Private Sub IProgressBar_Hide()
Me.Hide
End Sub

The only differences between this code and the "plain" Progress Bar form we saw in Chapter 10 Userform Design and Best Practices are that the Title, Text, Min, Max and Progress properties have been exposed via the IProgressBar interface and we've added our own Show and Hide methods to show and hide the form using that interface. The difference between the IntelliSense displays when using our custom IProgressBar interface instead of the form's default interface can be seen in Figure 11-4 and Figure 11-5. By implementing the interface, the consumer of our progress bar form has a much clearer display of the properties and methods that should be used to control the progress bar. Figure 11-4 shows the IntelliSense popup we get if we add the progress bar properties directly to the form, and Figure 11-5 shows the much simpler IntelliSense list we get when using the custom interface.

Figure 11-4. Using the Form's Default Interface Shows All the Userform's Properties in the IntelliSense List, Obscuring the Ones for the Progress Bar Itself

Figure 11-5. Using the Custom IProgressBar Interface Limits the Intelli Sense List to Only the Items We Want to Expose, Simplifying the Use of the Form

The CProgressBar Class

After we know that the consumer of our progress bar is accessing it through our custom interface, we are free to modify the implementation of the progress indication in any way we like. As long as we keep the interface the same, we know the code that uses the class will continue to work. The opposite is also true; as consumers of the class, we know as long as the interface is kept the same, the creator of the class cannot change the name of any of the properties or methods and in doing so break our code. By way of example, the code in Listing 11-17 implements the interface using a class module instead of a userform and displays the progress on Excel's status bar.

Listing 11-17. The CProgressBar Class Implementing the IProgressBar Interface

' Class to show a progress indication in the status bar.
' Implements to IProgressBar interface to allow easy switching
' between showing the progress on the status bar (this class)
' or on a userform (the FProgressBar form).
Option Explicit
'Implement the IProgressBar interface
Implements IProgressBar
'Module-level variables to store the property values
Dim msTitle As String
Dim msText As String
Dim mdMin As Double
Dim mdMax As Double
Dim mdProgress As Double
Dim mbShowing As Boolean
Dim msLastCaption As String
'Assume an initial progress of 0-100
Private Sub Class_Initialize()
mdMin = 0
mdMax = 100
End Sub
'Set and get the title
Private Property Let IProgressBar_Title(RHS As String)
msTitle = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IProgressBar_Title() As String
IProgressBar_Title = msTitle
End Property
'Set and get the descriptive text
Private Property Let IProgressBar_Text(RHS As String)
msText = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IProgressBar_Text() As String
IProgressBar_Text = msText
End Property
'Set and get the minimum value for the bar
Private Property Let IProgressBar_Min(RHS As Double)
mdMin = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IProgressBar_Min() As Double
IProgressBar_Min = mdMin
End Property
'Set and get the maximum value for the bar
Private Property Let IProgressBar_Max(RHS As Double)
mdMax = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IProgressBar_Max() As Double
IProgressBar_Max = mdMax
End Property
'Set and get the progress point
Private Property Let IProgressBar_Progress(RHS As Double)
mdProgress = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IProgressBar_Progress() As Double
IProgressBar_Progress = msprogress
End Property
'Show the progress bar
Private Sub IProgressBar_Show()
mbShowing = True
mdLastProgress = 0
UpdateStatusBar
End Sub
'Hide the progress bar
Private Sub IProgressBar_Hide()
Application.StatusBar = False
mbShowing = False
End Sub
'Private routine to show the progress indication
'on the status bar
Private Sub UpdateStatusBar()
Dim dPerc As Double
Dim sCaption As String
'Calculate the progress percentage
If mdMax = mdMin Then
dPerc = 0
Else
dPerc = Abs((mdProgress - mdMin) / (mdMax - mdMin))
End If
'Create the caption
If Len(msTitle) > 0 Then sCaption = msTitle
If Len(msTitle) > 0 And Len(msText) > 0 Then
sCaption = sCaption & ": "
End If
If Len(msText) > 0 Then sCaption = sCaption & msText
'Calculate and add the formatted percentage
sCaption = sCaption & " (" & Format$(dPerc, "0%") & ")"
'Update the status bar if it's changed
If sCaption <> msLastCaption Then
msLastCaption = sCaption
Application.StatusBar = sCaption
End If
End Sub

The calling code can very easily switch between using either the form or the status bar for the progress display (perhaps according to a user's preference), as shown in Listing 11-18.

Listing 11-18. Using the IProgressBar Interface Allows the Choice Between the Form and the Class

Sub LongRoutine(bProgressInForm As Boolean)
'Always use the IProgressBar interface
Dim pbProgBar As IProgressBar
Dim iCounter As Integer
If bProgressInForm Then
'Use the progress bar form
Set pbProgBar = New FProgressBar
Else
'Use the status bar class
Set pbProgBar = New CProgressBar
End If
'The rest of the code is unchanged
pbProgBar.Title = "Professional Excel Development"
pbProgBar.Text = "Preparing report, please wait..."
pbProgBar.Min = 0
pbProgBar.Max = 1000
pbProgBar.Progress = 0
pbProgBar.Show
For iCounter = 0 To 1000
pbProgBar.Progress = iCounter
Next
pbProgBar.Hide
End Sub