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:
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…