Using Classes – Part 3

In Part 2, we looked at the first of our two ‘Model’ classes: Resource. This amalgamates its booking information, and can therefore answer availability queries (like keeping and looking in a diary). We now turn our attention to the second Model class: Job.

This differs from Resource in that it has a direct correspondence to records on our Bookings worksheet. So some features of the class are to do with its persistence: getting data off and back on to the worksheet. Getting data for a Job off the worksheet will be done using the VLOOKUP function. Writing data to the worksheet just involves setting Range.Value.

The key feature of Job is AcquireResource: this creates a Resource object for each available resource, in turn, and queries its availability, stopping when it gets a positive response. This makes the ordering of resources (in a list on another worksheet) significant: a later available resource will only get allocated if all earlier resources are unavailable. This minimises the number of resources used, which is what we want (a more balanced allocation strategy is possible).

So to the code. Internally (privately) we hold the attributes of the Job:

Dim jid As Integer
Dim jstart As Date
Dim jfinish As Date
Dim jresource As String

The invariant is slightly tricky in that a Job is valid (for purposes of storing on the worksheet) only if it has an allocated resource. However, it’s possible that a Job ‘in preparation’ might not (yet) have a resource, and we have to allow for this. Rather inelegantly, we’ll just use a placeholder value “none”.

Public Function Invariant() As Boolean
    Invariant = Isvalid(jid, jstart, jfinish, jresource) Or _
                Isvalid(jid, jstart, jfinish, "none")

End Function

Public Function Isvalid(Id As Integer, start As Date, _
             finish As Date, resource As String) As Boolean
    Isvalid = Id > 0 And (start <= finish) And _
                                Len(resource) > 0

End Function

Now for the Class_initialize handler. This needs to increment the current maximum Job Id. If there are no Jobs at all, we need to handle the error thrown by an empty dynamic range (see Part 2).

Public Sub Class_initialize()
    'post: (jid > 0) and (jstart = jfinish)

    Dim maxid As Integer

    On Error Resume Next    'Dynamic ranges not found if empty
    maxid = WorksheetFunction.Max(Range("Jobs"))

    jid = maxid + 1     'On error, maxid = 0
    jstart = Date
    jfinish = Date

End Sub

Notice that the object attempts to AcquireResource; this might succeed or fail. So we don’t store the Job immediately; this is done by a client object (in this case our Form).

The four attributes (see earlier) each have a standard Get procedure. start and finish have standard Let procedures. resource has no Let procedure; it is set only by AcquireResource. Id has a Let procedure which (like the Resource class) targets the object to an existing Job, or re-initializes the object:

Property Let Id(newid As Integer)
        'Target Job at a given Id, retrieving data fields
        'Re-initialize by giving a nonexistent Id (e.g. 99)
        'pre: newid > 0

    Dim lookupid As Integer
    Dim maxid As Integer

    On Error GoTo NoJobs    'Dynamic ranges not found if empty
    lookupid = WorksheetFunction.Lookup(newid, Range("Jobs"))

    If lookupid = newid Then 'newid found
        jid = newid

    Else 'newid not found - initialize new Job
        Call Class_initialize
    End If

    Exit Property
        Call Class_initialize

End Property

For an existing Job, we need to retrieve the data from the worksheet. This is just a bunch of VLOOKUPs:

Private Sub Retrieve()
    jstart = WorksheetFunction.VLookup(jid, _
                Range("Bookings"), 2, False)
    jfinish = WorksheetFunction.VLookup(jid, _
                Range("Bookings"), 3, False)
    jresource = WorksheetFunction.VLookup(jid, _
                Range("Bookings"), 4, False)
End Sub

The other side of the persistence is storing the object. There are several different cases: first ever Job, existing Job, subsequent new Job:

Public Sub Store()

    Dim rownum As Integer

    If jid > 1 Then 'Not first Job
        rownum = WorksheetFunction.Match(jid, Range("Jobs")) _
                + Range("Job").Row       'Allow for header row

        If rownum <> jid + Range("Job").Row Then
                                'new Job; append to data rows
            rownum = rownum + 1
            Range("Job " & rownum & ":" & rownum).Value = jid
        End If
    Else ' First Job
        rownum = Range("Job").Row + 1       'First data row
        Range("Job " & rownum & ":" & rownum).Value = jid
    End If

    Range("Start " & rownum & ":" & rownum).Value = jstart
    Range("Finish " & rownum & ":" & rownum).Value = jfinish
    Range("Resource " & rownum & ":" & rownum).Value = jresource
            'Ranges Start, etc, are whole-columns

End Sub

I expect that there are different ways of doing this. Here I’m using the MATCH function. In the Range.Value assignments, I’m intersecting a named whole-column range with the appropriate whole-row range:
Range(“SomeColumn R:R”).

Finally, we have AcquireResource, which creates Resource objects, and queries their availability. There is a simple list of resource Ids in the range “Resources”, on another worksheet.

Private Sub AcquireResource()
    'pre: Range("Resources").Count > 0
    Dim resid As String
    Dim res As resource

    jresource = ""

    Dim i As Integer
    For i = 1 To Range("Resources").Count
        resid = WorksheetFunction.Index( _
                                    Range("Resources"), i, 1)
        Set res = New resource
        res.Id = resid
        If res.IsAvailable(jstart, jfinish) Then
            jresource = resid
            Exit For
        End If

End Sub

Note the precondition (which you can check in the Immediate window); this means that the procedure does not need to handle the empty-range error case, in contrast to other procedures.

The final stage is to provide a View of our Job objects: in other words a UserForm that displays and allows a user to set properties of a Job. Coming shortly…


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

April 2009
« Mar   May »

%d bloggers like this: