Listing The Complete Setting Class

Option Explicit

' private class variables Private mwsSettings As Worksheet Private mrgSetting As Range Private mbAllowEditing As Boolean

private class constants


















= 4

Enumeration for the kinds of Setting types

Enumeration for the kinds of Setting types

Enum setSettingType setPrivate = 0 setReadOnly = 1 setReadWrite = 2 setReadProtectedWrite = 3 End Enum don't display in user inteface read-only setting read-write setting read-write with password

' Setting Description Property Get Description() As String If mrgSetting Is Nothing Then Description = ""


Description = mrgSetting.Offset(0, DESCRIPTION_OFFSET).Value End If End Property

Property Let Description(PropertyDescription As String) If mrgSetting Is Nothing Then UninitializedError

' mbAllowEditing is managed by the ' EditMode method. If mbAllowEditing Then mrgSetting.0ffset(0, DESCRIPTION_OFFSET).Va1ue = _ PropertyDescription


ReadOnlyError End If End If End Property

' setting EventHandler - represents a procedure that ' gets called automatically when the setting's value ' changes

Property Get EventHand1er() As String If mrgSetting Is Nothing Then EventHandler = ""


EventHandler = mrgSetting.0ffset(0, CHANGE_EVENT_0FFSET).Va1ue End If End Property

Property Let EventHand1er(EventHand1erProcedure As String) If mrgSetting Is Nothing Then UninitializedError


' mbAllowEditing is managed by the ' EditMode method. If mbAllowEditing Then mrgSetting.0ffset(0, CHANGE_EVENT_0FFSET).Va1ue = _ EventHandlerProcedure


Read0n1yError End If End If End Property

' The settings are ordered by row ' on the Settings worksheet. Because this worksheet ' includes one row for column headings, you can ' get the index of the setting by looking at ' the row of the setting and subtracting one. Property Get Index() As Long

If mrgSetting Is Nothing Then Index = -1


Index = mrgSetting.Row - 1 End If

End Property

Property Get Name() As String

If mrgSetting Is Nothing Then Name = ""


Name = mrgSetting.Value End If End Property

Property Let Name(PropertyName As String)

' Name is implemented as a read-only property ' so you can create dependencies on Setting ' names in your code. End Property

Property Get SettingType() As setSettingType If mrgSetting Is Nothing Then SettingType = -1


SettingType = mrgSetting.Offset(0, TYPE_OFFSET) End If End Property

Property Let SettingType(SettingType As setSettingType) If mrgSetting Is Nothing Then UninitializedError


If mbAllowEditing Then mrgSetting.Offset(0, TYPE_OFFSET).Value = SettingType


ReadOnlyError End If End If End Property

Property Get Value() As Variant If mrgSetting Is Nothing Then Value = ""


Value = mrgSetting.Offset(0, VALUE_OFFSET) End If End Property

Property Let Value(PropertyValue As Variant) If mrgSetting Is Nothing Then UninitializedError

If mbAllowEditing Then

' ok, change the value mrgSetting.0ffset(0, VALUE_OFFSET) ' call any procedures specified by ' in the event of a change ExecuteEventHandler


ReadOnlyError End If End If End Property

Public Function De1ete() As Boolean Delete = False

If mrgSetting Is Nothing Then UninitializedError


If mbAllowEditing Then mrgSetting.EntireRow.Delete xlUp Set mrgSetting = Nothing Delete = True


ReadOnlyError End If End If End Function

Public Function ChangeEditMode(A11owEditing As Boolean, _ Optional Password As Variant) As Boolean

If AllowEditing Then

Select Case Me.SettingType

Case setSettingType.setPrivate

' Private settings are settings used ' for programatic purposes or otherwise ' that should not be displayed on any ' user interface but can be freely ' modified programmatically mbAllowEditing = True

Case setSettingType.setReadOnly

' Settings that are not intended to ' be changed by users but are useful ' to know. Never allow EditMode on these. mbAllowEditing = False

Case setSettingType.setReadWrite

' Settings that can be freely modified

.Value = PropertyValue the setting

' by the user mbAllowEditing = True

Case setSettingType.setReadProtectedWrite ' Settings that can be read but only ' changed by users that know the password If IsMissing(Password) Then mbAllowEditing = False


If Va1idPassword(CStr(Password)) Then mbAllowEditing = True

Else mbAllowEditing = False End If End If Case Else

' unknown setting type mbAllowEditing = False

End Select

Else mbAllowEditing = False End If

ChangeEditMode = mbAllowEditing End Function

Public Function GetSetting(SettingName As String) As Boolean Dim IRow As Integer Dim bFoundSetting As Boolean

Set mrgSetting = Nothing bFoundSetting = False mbAllowEditing = False

Do Until IsEmpty(mwsSettings.Ce11s(1Row, 1))

If UCase(mwsSettings.Ce11s(1Row, 1).Va1ue) = _ UCase(SettingName) Then

Set mrgSetting = mwsSettings.Ce11s(1Row, 1) bFoundSetting = True Exit Do

End If



= bFoundSetting

End Function

Private Sub Uninitia1izedError()

Err.Raise vbObjectError + 101, "Setting Class", _

"The setting has not been properly initialized. " & _ "Use the GetSetting method to initialize the setting."

End Sub

Private Sub ReadOn1yError()

Err.Raise vbObjectError + 102, "Setting Class", _ "The setting you are trying to change is " & _ "either read-only, requires a password, or " & _ "you have not put the object in edit mode " & _ "using the EditMode method."

End Sub

Private Sub C1ass_Initia1ize()

' do not allow editing by default mbAllowEditing = False

' need to point the mws worksheet variable to the ' Settings worksheet

If WorksheetExists(ThisWorkbook, SETTINGS_WORKSHEET) Then

Set mwsSettings = ThisWorkbook.Worksheets(SETTINGS_WORKSHEET)


Set mwsSettings = Nothing

Err.Raise vbObjectError + 100, "Setting Class", _ "The worksheet named " & SETTINGS_WORKSHEET & _ " could not be located." End If End Sub

' Determines if a given worksheet name exists in a workbook Private Function WorksheetExists(wb As Workbook, sName As String) _ As Boolean Dim s As String

On Error GoTo WorksheetExistsErr s = wb.Worksheets(sName).Name

WorksheetExists = True Exit Function


WorksheetExists = False End Function

' Validate password by comparing it against the value ' given by the Password setting on the Settings worksheet. ' Obviously, this assumes that the worksheet is managed such ' that it cannot be easily retrieved/discovered. ' WARNING: this provides only very basic security and should ' not be used to protect sensitive data.

Private Function Va1idPassword(sPassword As String) As Boolean Dim oSetting As Setting Dim bValid As Boolean bValid = False

Set oSetting = New Setting

If oSetting.GetSetting("Password") Then If oSetting.Value = sPassword Then bValid = True

Else bValid = False End If

Else bValid = False End If

Set oSetting = Nothing ValidPassword = bValid End Function

Private Sub ExecuteEventHand1er() On Error Resume Next ' make sure there is an Event Handler ' for the setting If Len(Me.EventHandler) <> 0 Then ' call the procedure specified ' by the EventHandler property Application.Run Me.EventHandler End If End Sub

The key to the implementation of this class is the class variable mrgSetting, a range object that represents the cell associated with the current setting's name. When you use the x_OFFSET constants defined near the top of the class, it is possible to use the Offset method on the mrgSetting object to retrieve all of the other information associated with the Setting object.

Because the mrgSetting variable is absolutely essential to the operation of this class, it is imperative that you make sure that this range is actually pointing to something in nearly every procedure. You may wonder then, why the Class_Initialize procedure doesn't set this variable. The reason is that you have no way to specify which range the Class_Initialize procedure should point mrgSetting to. It would be nice to have a way to specify an input parameter to this procedure. Unfortunately, this is a limitation of VBA in regards to developing classes. In other languages, such as C++, for example, you can create initialization procedures that have input parameters.

In order to get around this limitation you need to use a method named GetSetting. This method's sole purpose is to set the mrgSetting variable so it refers to the setting with the specified name. You'll see that the Settings object knows how to call this method in the Item property so that end users (developers who use the class) won't need to perform the awkward process of creating the object and then using GetSetting to properly "initialize" it.

If a procedure notices that the mrgSetting variable is not set up correctly, it either raises an error (using the UninitializedError procedure) in the case of a Property Let procedure, or it reports either an empty string or the value —1 in the case of a Property Get procedure.

Another piece of functionality that needs special attention is the process of implementing the SettingType property. The SettingType property allows you to designate a setting as private, read-only, read-write, or read-protected write. Consequently, it won't suffice to create any old Property Let procedures. You need to be conscious of the fact that it may or may not be possible to modify the value depending on the value of the SettingType property and whether or not an appropriate password was supplied. In order to keep track of whether or not the Setting can be edited, the class uses a Boolean variable named mbAllowEditing. This variable is false by default and can only be changed using the ChangeEditMode method.

ChangeEditMode is the method in charge of determining whether or not a setting can be modified. The AllowEditing parameter is a required Boolean parameter. The Password parameter is optional and is only required or applicable if the setting's type is read-protected write. Notice the use of the IsMissing function in the setSettingType.setReadProtectedWrite case. IsMissing is a VBA function you can use to test whether or not optional parameters were passed into the procedure.

NOTE Parameters specified with the Optional keyword can't have any required parameters after them in the parameter list.

WARNING IsMissing only works with Variant data types. If you use it against other data types, it always returns false.

If the password is present and the setting type is read-protected write, then ChangeEditMode relies on the ValidPassword method to validate the password. ValidPassword is a private method and can't be seen or called from procedures external to the Setting class. ValidPassword assumes a setting on the Settings worksheet called Password. If this setting isn't present, it won't validate the password.

This password validation scheme highlights an important benefit of using classes. As I mentioned in the last chapter, because consumers of classes depend on the interface of a class and not its implementation, you can change implementation details without breaking any code that uses the class. If you require a more secure scheme to password-protect settings, you can change the implementation details of the ValidPassword function.

Fake Is Fine

The Setting class contains a powerful capability—so powerful that it needs its own section. This is the surprise I mentioned earlier in the chapter and it is huge. While perusing Listing 12.1, did you pick up on the EventHandler property and the private ExecuteEventHandler procedure? Are you hoping it is what you thought it was? That is, a way to dynamically execute a setting-specific procedure whenever the setting's value changes? Although you can't create "real" events for your classes with VBA, you can fake it—and for most purposes, faking it does just fine.

The Setting class knows when setting values change because it controls how they get set with the Property Let procedure. As mentioned in the last chapter, this is a key benefit of using Property procedures versus implementing class properties using public variables. This means that any property implemented using Property Let/Set provides you with the opportunity to create functionality similar to events. The question is how will you decide which procedure to call when an event occurs?

You could hard code the appropriate event procedures within the class, but this would be problematic. When developing your own classes, one of your goals is that after you create them, you leave them alone. Therefore, you don't want to put anything in the class that might change. So you certainly don't want to hard code event procedures in the class that are likely to be application specific. That's where the Run method of the Application object comes into play.

The Run method provides you with the ability to call any public procedure in the current project. Because you can pass variables to the Run method, you don't need to know the procedure names at development time. Instead, you can place these procedures in a handy place (like the Settings worksheet) and look them up dynamically from your procedures. For the Setting class, it makes sense to include the setting's change event procedure as a property of the Setting itself. Then, in order to call the appropriate event procedure, all you need to do is call the procedure given by the setting's EventHandler property using Application.Run whenever the setting's value changes.

0 0

Post a comment