Using Classes – Part 2

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

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)
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.


3 Responses to “Using Classes – Part 2”

  1. 1 actithHet April 4, 2009 at 12:20 am

    Great site this and I am really pleased to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

  2. 2 Slelpivava April 10, 2009 at 7:00 pm

    formidable site this brill to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

  1. 1 Using Classes - Part 3 « Roy MacLean’s VBA Blog Trackback on April 2, 2009 at 4:37 pm

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

March 2009
« Feb   Apr »

%d bloggers like this: