Listing The Settings Classa Pseudo Collection of Setting Objects

Option Explicit

' class constants

Private Const SETTINGS_WORKSHEET = "Settings"

Private Const NAME_COLUMN = 1

Private Const VALUE_COLUMN = 2

' class variables

Private mwsSettings As Worksheet

' count of settings

Property Get Count()

Count = mwsSettings.Ce11s(65536, 1).End(x1Up).Row - 1

End Property

' adds a new setting. returns setting object ' associated with the new setting. Public Function Add(Name As String) As Setting Dim IRow As Long Dim oSetting As Setting

' make sure a setting with this name

' doesn't already exist

'Set oSetting = Me.Item(Name)

If Not SettingExists(Name) Then

' find the last used row and move down one row

IRow = mwsSettings.Ce11s(65536, 1).End(x1Up).Row + 1

' add the name of the new setting mwsSettings.Ce11s(1Row, 1) = Name

' set a reference to it

Set oSetting = Me.Item(Name)


' the item already exists

Err.Raise vbObjectError + 201, "Settings Class", _ "A setting named " & Name & " already exists." Set oSetting = Nothing End If

Set Add = oSetting End Function

' deletes ALL settings

Public Function De1ete() As Boolean mwsSettings.Range(mwsSettings.Ce11s(2, 1), _

mwsSettings.Ce11s(65536, 4)).C1earContents Delete = True End Function

' retrieves a setting by index or name ' retrieves by index if Index is numeric ' retrieves by name if Index is not numeric Public Function Item(Index As Variant) As Setting Dim IRow As Long Dim IFoundRow As Long Dim oSetting As Setting Dim sName As String

Set oSetting = New Setting

' if Index is numeric then assume

' that we are looking by index

' if Index is not numeric then assume

' that we are looking by name

If IsNumeric(Index) Then

' get the name of the setting associated with ' the index. Row of setting = Index + 1 (header row) sName = mwsSettings.Ce11s(Index + 1, 1).Va1ue ' make sure we got a name rather than an empty ' cell

' set a reference to the setting If oSetting.GetSetting(sName) Then Set Item = oSetting


Err.Raise 9, "Settings Class", _ "Subscript out of range."

End If


Err.Raise 9, "Settings Class", _ "Subscript out of range."

End If


If oSetting.GetSetting(CStr(Index)) Then Set Item = oSetting


Err.Raise 9, "Settings Class", _ "Subscript out of range."

End If End If End Function

' performs a reverse-lookup. looks up a setting by value ' rather than by name.

Public Function ItemByVa1ue(Va1ue As Variant) As Setting Dim IRow As Long Dim oSetting As Setting Dim bFound As Boolean

Set oSetting = New Setting bFound = False

For IRow = 2 To mwsSettings.Ce11s(65536, 1).End(x1Up).Row

If Value = mwsSettings.Ce11s(1Row, VALUE_COLUMN).Va1ue Then If oSetting.GetSetting( _

mwsSettings.Ce11s(1Row, NAME_COLUMN).Va1ue) Then Set ItemByValue = oSetting


Err.Raise 9, "Settings Class", _ "Subscript out of range."

End If bFound = True

Exit For End If


If Not bFound Then

Set ItemByValue = Nothing Err.Raise 9, "Settings Class", _ "Subscript out of range."

End If End Function

Private Sub Class_Initialize()

' 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 + 200, "Settings 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 bWorksheetExistsErr s = wb.Worksheets(sName).Name

WorksheetExists = True Exit Function bWorksheetExistsErr:

WorksheetExists = False End Function

Private Function SettingExists(SettingName As String) As Boolean Dim oSetting As Setting

On Error GoTo ErrHandler

Set oSetting = Me.Item(SettingName)

SettingExists = True Set oSetting = Nothing Exit Function


SettingExists = False

End Function

This class has a couple of noteworthy sections starting with the Add method. One of the rules enforced by the Add method is that setting names must be unique. In order to check if the name provided to Add is unique, the Add method calls the private function SettingExists. SettingExists tries to set a reference to it using the Item method of the Settings class. The keyword Me is simply a convenient way to refer to the class from within the class itself. Anyway, if an error is generated, then SettingExists reports that the setting doesn't exist. This algorithm is identical to the one used by WorksheetExists.

When the setting is added, basically all that happens is that you find the first empty row, enter the setting name in the first column or the first empty row, and then set a reference to the newly added setting.

The Item method is the other noteworthy passage. Just like real collection classes, the goal here is to allow the ability to use either an index number or a setting name as a parameter. For example, if a setting named "Fiscal Year" is the second setting, you want to be able to refer to it using either of the following statements.

' refer to a setting by index number Set setFiscalYear = MySettings.Item(2) ' refer to a setting by name

Set setFiscalYear = MySettings.Item("Fiscal Year")

In order to achieve this goal, the input parameter (named Index as is customary with collection classes) must be declared as a Variant data type. The Item method assumes that if the parameter is a number, then it must represent the setting index. Otherwise it treats the parameter as a setting name. The implication of this is that it is wise to avoid using numeric setting names. If a setting name is a number, the only way to set a reference to that setting is by using the index number. For example, if you have a setting named 423 and try to set a reference by name (e.g., MySettings.Item("423")), the Item method would try and retrieve the setting associated with the 424th row, which may or may not contain a setting. If it does contain a setting, chances are that it will be the wrong one. If the specified setting isn't found, Item raises a "Subscript out of range" error, much as other collection classes would.

The ItemByValue method is a way to retrieve a setting by looking at setting values rather than setting names. This method loops through the setting values until it finds a value that matches, and then it returns a reference to the associated setting. If the value is not found, a "Subscript out of range" error is raised.

The Delete method is simple—just clear the contents of the cells representing A2:D65536. The Count property is also trivial. The Count of the settings is just the row number of the last used row minus one for the heading row (row 1).

0 0

Post a comment