In Part 1, we introduced a resource-allocation problem: we need to find out the availability of a Resource, based on multiple Job records in a worksheet list:
We need to use a Resource class which will amalgamate the existing bookings, and answer availability queries with respect to these.
The definition of Resource is in a Class Module ‘Resource’.
Internally (that is, privately), Resource holds and Id, plus a list of starts and a list of finishes:
Dim rid As String Dim rstarts As List Dim rfinishes As List
The List class is quite basic: we need to be able to append (Add) to the end, and retrieve an item by index (GetNth). This is a typical ‘helper’ role for such a collection class.
Before going further, we should think about the class invariant: what is always true of a Resource object. Because this is something we will want to check during testing, it’s worth stating this as code:
Public Function Invariant() As Boolean Invariant = Isvalid(rid, rstarts, rfinishes) End Function Public Function Isvalid(Id As String, starts As List, _ finishes As List) As Boolean Isvalid = Len(Id) > 0 And _ (starts.Count = finishes.Count) End Function
The reason for separating out an IsValid function is that this is a general checking function, not a check of this particular object. So it can be used by, say, a form, to check candidate values (we’ll do this with Job).
Next we have the Class_initialize procedure:
Public Sub Class_initialize() rid = "none" Set rstarts = New List Set rfinishes = New List End Sub
It might seem that this should take an Id as input argument. However, in VBA this is an event handler (analogous to UserForm_initialize), not a ‘constructor’, as in other OO languages. The Id has to be set by a subsequent call (coming soon). This is a bit inelegant, because the job of the initialization is to ensure the class invariant: that is, set up a valid initial state. This means that we have to provide some non-empty placeholder value for the Id. The two Lists are newly created and therefore have length zero.
Now, we need to be able to get and set the Id property:
Property Get Id() As String Id = rid End Property Property Let Id(newid As String) rid = newid PopulateDates End Property
Every other language that I know talks about ‘getters’ and ‘setters’. Unfortunately, VBA insists on distinguishing basic values (integer, etc) from objects. We see this in the need to say ‘Set myrange = Range(“Jobs”). So the setter for a basic value is Let, not Set. I trust that’s clear.
Having set (let?) the Resource Id, we can then go to the worksheet and get the booking dates. PopulateDates goes through the Job records, checking whether Resource is the same as this Resource’s Id, and if so adding the dates to the lists.
Private Sub PopulateDates() 'Get the start-finish date pairs from the Bookings range 'and add them to the respective lists. 'post: rstarts.count = ' WorksheetFunction.Countif(Range("Resource"),rid) Dim i As Integer Dim start As Date, finish As Date Dim resource As String On Error GoTo subend For i = 1 To Range("jobs").Count start = WorksheetFunction.VLookup(i, _ Range("Bookings"), 2, False) finish = WorksheetFunction.VLookup(i, _ Range("Bookings"), 3, False) resource = WorksheetFunction.VLookup(i, _ Range("Bookings"), 4, False) If resource = rid Then 'it's ours rstarts.Add start rfinishes.Add finish End If Next subend: End Sub
There’s a slight complication here: the Ranges “Jobs” (column B) and Range “Bookings” (the whole table) are defined as dynamic named ranges. So the definition of “Jobs” is:
(Bookings here is the Worksheet name). This is fine and dandy, but when there are no jobs in the table, a call to Range(“Jobs”) throws an error. Hence the error handling, which simply jumps to the end (there are no jobs to inspect).
Note also the postcondition comment, as discussed in an earlier posting. At the end of the procedure, the number of items in the starts list should be the same as the number of times the Resource Id appears in the Resource column of the worksheet. This is something that we can use in our tests.
Finally, we come to raison d’etre for the class: the availability query. For each of the currently booked periods, we need to check whether the specified period overlaps or not:
Public Function IsAvailable(start As Date, finish As Date) _ As Boolean 'Is this Resource available for the specified period '(inclusive of start and finish dates)? 'pre: start <= finish IsAvailable = True Dim i As Integer Dim s As Date, f As Date For i = 1 To rstarts.Count s = rstarts.GetNth(i) f = rfinishes.GetNth(i) IsAvailable = IsAvailable And _ (finish < s Or start > f) Next End Function
Just to keep things simple, the whole of the starts/finishes lists are traversed. You could opimise it somewhat by breaking out of the loop as soon as the result is False.
Note also the precondition comment, which shifts responsibility for ensuring valid date ordering on to the calling object. As it happens, this is a Job object, which in turn shifts it on to the Form, which ensures it by means of conditionals in some event handlers (which we’ll see later).
That’s it for the Resource class. In the next posting we’ll look at the Job class, and particularly its relationship with the job records on the worksheet.