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
AcquireResource
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
Retrieve
Else 'newid not found - initialize new Job
Call Class_initialize
End If
Exit Property
NoJobs:
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
Next
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…
1 Response to “Using Classes – Part 3”