Using Classes – Part 4

schedule_ws2

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

schedule_form

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
    Display
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
        Display
    Else
        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
        Display
    End If
End Sub

The OK button tells myjob to store itself:

Private Sub OKCommand_Click()
    If Isvalid Then
        myjob.Store
        Display
    Else
        InvalidMsg
    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 & _
            myjob.ValidString
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:

schedule_design2

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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




April 2009
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

%d bloggers like this: