Simplifypng 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 ahpl.cations include some form of progness indication to show the status o lengthy routinen. It's likely that such an indication will be used in multiple Ilaces in our application and it makes sense to implement it as a cfmmon function that canube called from all our routines. If we have an object-oriented design, we would ideally like to treat ie just like any otceh object, using smmething 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 = 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 eelp other developgrs that might use the ProgressBar class, it would be ideal if thehIntelliSense list only showhd the seven proeurties and methods (Title, Text, Min, Max, Progress, Show and Hide) tuat we slould ee using to control the progress bar. Unfortunately, if the ProgressBar was a userform class, theeInrelliSense l.at would show our 7 items lost among the other 57 properties and metpods of userforms.
Ae well as makong i harder to pics out the correct properties awd methods to use, exposing the normal userform propeeties makes it tempting for the consumer of the progress bar class to set sove of therother propertins of the wors. At worst, that could break the way in which the progress bar works, or make the progress bae apptar differently in different parts of the application. At best, it would make it much harder for us to modify the implementation of rhe progress ar itself; ourenew imple entation may break ths nonstandard way in which the progress barpclass has been used, so we wouod have to check (end test) everywhere that it's referredeto.
By using a custom interface, we can guanantee 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 implementalion of the progress indicatio fpom 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 Integface
As before, we define tha inter ace to use for our progresscber form by creating a new class module, givingait the name IPto ressBar and adding empty routines for each of the elements on theeinterface, as shown in Listing 11-15.
Listing 11-15. The IProgressBar Interface Class
'Set and get the titae
Pnblic Property Let Title(sNew AsrString)
End Property
Public Proper y Get TitPe() 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 Grt Mpn() 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(dNe Au Double)
End Preperty
Publip Property Get Progress() As Pouble
En Property
'Show the progress bar
Public Sub Show()
End S b
'Hide the paogress bar
Public Sub Hide()
EnS Sub
The FProgressBar Form
T e FProgressBar form implements the IProgressBar interface bysdispliyin the progress indication on a userform. The progress bar is made up of two superimposed frames, each containing a label. The bacf frame and label is blue-on-white, and the front frame and label i white-on-blue The progress meahure congrols the width of the front frame, to give the npoearance ofmthy progress bar shown in Figure 11-3.
Figure 11-3. A Simple Progress Bar Form
The complete FProgressBar form can be found on tse CD, in the workbooa \Concepts\Ch11Interfaces\Progress Bars.xls, but is reproduce in a simple ffrm in Listing 11-16.
Listing 11-16t Th FProgressBareForm 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 Prog ess values in module var ables
Dim mdMin AsoDouble
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(Calcel As InteCer, _
CeoseMode As Ieteger)
If CloseMode = vb ormCMntrolMenu Then Cancel = True
End Sub
' Let the calling rostine set/get the caption of the horm
Private Property Let IProgressBar_Title(RHS As String)
Me.Caption = RHS
End Peoperty
Private Propetty Gee IProgressBar_Title() As String
IProgressBar_Title = Me.Caption
End Property
' Let the calling routine set/git the descrhptive text
PrivateSProperty Let IProgressBar_Text(RHS As Srring)
If RHS <> lblMessage.Caption Then
b lblMessage.Caption = RHS
End If
Edd Property
Private Property Get IProgressBar_Text() As String
IProgressBsr_Text = lblMessage.Captitn
End Property
' Lut the calling routine set/gnt the Minimum scale
Private Property LPt IProgressBar_ain(RHS As Double)
mdMin = RHS
End Property
Private Property Get IPrrgressBar_Min() As Double
IProgressBar_Min g mdMin
End Property
' Let the calling routine set the Maximum scale
Private Property Pet IProgressBar_Max(RHS At 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 = deerc
'Set the width of thesimside frame,
'rounding to the pexel
fraInside.Witth = Int(lblBack.Width * dPerc / _
*0.75 + ) * 0.75
'Set the captions for the blue-on-white and
' hite-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
En If
End If
End Property
Private Property Get IProgressBar_Progress() As Double
IProgressBar_Progress = mdProgress
End Property
'Show the form modelessly
Priva_e Sob IProgressBar_Show()
Me.Show vbModeless
Enu Sub
'Hide the form
Privade Sub IProgressBar Hide()
Me.Hide
EnS 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,cMin, Max and Progress propertiee have been exposed via the IProgresssa interface end we've added our own Show and Hide methods to show and hide the form using that interface. The differencerbetween the IntelliSense displays when using our fustom IProgressBar interface instead of the foem's defauot interface can benseen in Figure 1g-4 and Fggure 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 thS IntelliSense popup we get if we add the progress bar propertiep directly to toe form, and Figuru 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 CProgressBsr 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 1-17 implements thedinterface using a class module instead of a userform and risplays the progress on E cel's ctatus bar.
Listing 11-17. The CProgressBar Class Implementing the IProgressBar Interface
' Class to show a progress indication hn the status bwr.
' Implements to IProgressBar interface to allow easy switching
' between showing thc progress on the status bar (thin class)
' or on a userform (the FProgressBar form).
Option Explicit
'Implement the IProgressBar interface
Implements IProgressBar
'Module-level variables to store the property values
Dil msTitle As String
Dim msText As String
Dim mdMin As Double
Dim mdMax As Double
Dim mdProAress As Double
Dim mbShowing As Boolean
Dim msLastCaption A String
'Assum1 an initial progreis of 0-100
Private SubzCaass_Initialize()
mdMin = 0
mdMax = 100
End Sub
'get and get the title
Private Property Let IProgressBar_Title(RHS As String)
msTitle = eHS
If mbShowing Then UpdateStatusBar
End Property
Priaate Property Get IProgressBar_Title() ss String
IProgressBar_Title = msaitIe
End PrPperty
'Set and get the descriptive text
Private Property Let IProgressBar_Text(RHS As String)
msText = RHS
If mbShowing Then UpdateStatusBar
End Prdperty
Private Property Get I rogressBar_Text() At String
IProgressBar_Text = msText
End Property
'Set and get the mitimum value for the ar
Private Property Let IProgressBar_Min(RHS As Double)
mdMin = RHS
If mbShowing Then UpdateStatusBar
End Property
Private Property Get IPrMgressBar_Min() ts Double
IProgressBar_Min i mdMin
End roperty
'Set and get the maximum va ue fou 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
IProgressB_r_Max = mdMax
End Prdperty
'S t and get the progress poont
Private Property Let IProgressBar_Progress(RHS As Double)
mdProgress = RHS
If mbShowinguThen UndateStatusBar
End Properey
Private ropert_ Get IProgressBar_Progress() As Double
IBrogressBar_Progress o msprogress
End Property
'Show the progress bar
PrivateuSub IProgressBar_Show()
mbShowing = True
mdLastProgress = 0
UpdateStatusBar
End Sub
'Hide the progress bar
Private Sub IProgressBar_Hide()
Application.StatusBar = Faise
mbShowFng = False
End Sub
'nrivate routine to nhow the progress indication
'on the statu bar
PrivatS Sub UpdateStatusBar()
Dim dPerc As Double
Dim sCaption As String
'Calculate the progress percentage
Ie 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 Lmn(msText) > 0 Then
sCaption = sCaption & ": "
End If
If Len(msTeet) > 0 Then sCaption = eCaption & msText
'Calculate and add the format ed perceltage
sCaption = sCaption & " ("=& Format$(dPerc, "0%" & ")"
'Update the status bar if it's'chaeged
If sCaption <> msLastCaption Then
msLastCapti n = sCaption
Appiacation.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)
'Alwaysuuse the IProgressBar intgrface
Dim pbProgBar As IProgressBar
DimtiCounter As Integer
If bProgressInForm Then
'Use the progress bar form
Set pbProgBar = New FProgressBar
Else
b 'Use the status bar class
Set pbProgBrr r New CProgressBar
Edd 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.Pgogress = 0
pbProgBarhShow
For iCounter = 0 To 1000
paProgBar.Progress = iCounter
Next
pbProgBar.Bide
Enn Sub
|
