Using Classes – Part 4


The final step of our resource-allocation example is to provide a a form ‘view’ of our Job objects:


This form holds on to a ‘current’ Job object: an instance of the Job class described in Part 3. The form retrieves data from the current Job object, which in turn retrieves data from the worksheet records; the form does not access the worksheet directly. This means that the form does not need to know details of how the Job data is stored; it simply accesses the properties defined by the Job class.

UserForm_initialize creates its Job object, which initially is for a new (i.e. not yet existing) Job.

Dim myjob As Job

Private Sub UserForm_Initialize()
    Set myjob = New Job
End Sub

The Display procedure retrieves properties from the object, and sets the corresponding form controls:

Private Sub Display()
    IdText = myjob.Id
    StartText = Format(myjob.start, "dd-mmm-yy")
    FinishText = Format(myjob.finish, "dd-mmm-yy")
    ResourceText = myjob.resource
End Sub

The editable controls have AfterUpdate handlers, which update myjob. In the case of Id, there is a little validation:

Private Sub IdText_AfterUpdate()
    If IdText > 0 Then
        myjob.Id = IdText
        MsgBox ("Please enter an Id greater than zero")
        IdText = myjob.Id
    End If
End Sub

The plus and minus buttons for the dates similarly update myjob, with validation to ensure ordering:

Private Sub NextStartCommand_Click()
    If myjob.start < myjob.finish Then
        myjob.start = myjob.start + 1
    End If
End Sub

The OK button tells myjob to store itself:

Private Sub OKCommand_Click()
    If Isvalid Then
    End If
End Sub

The validation function and the associated message are held by the object, not the form:

Private Function Isvalid() As Boolean
    Isvalid = myjob.Isvalid(IdText, StartText, _
                        FinishText, ResourceText)
End Function

Private Sub InvalidMsg()
    MsgBox "Invalid Data: please ensure that:" & vbCrLf & _
End Sub

This keeps the semantics in the class (okay, we cheated a bit with the plus/minus buttons; should have called myjob.IsValid).

And that’s it! The ‘smart’ resource-finding behaviour is all within the Job object, not the form. So the form is really pretty simple.

Here’s the overall structure:


The objects (instances of our two classes) are created as needed, to serve as a ‘smart adapter’ layer between the form and the worksheet. In a more complex example, the objects might collate data from more than one worksheet (or even workbook). Also, we might have more than one view of a Job, or indeed of Resources.

A Zip of the workbook and the VBA modules is available here.

I’ll come back to the issue of testing classes in later posts.


1 Response to “Using Classes – Part 4”

  1. 1 geoffness April 7, 2009 at 12:02 pm

    This is a great series Roy, thanks very much for sharing it. I’ve recently been considering building a scheduling application which this would be a great template for. Cheers!

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: