Using Classes – Part 1

I’m a long-time Object-Oriented (OO) enthusiast (yea, even unto the years Before Java), so I think it’s about time that we looked at using classes in VBA.

Firstly, it’s the way to structure code once you get beyond the simple stuff (form code, functions, simple subs). Secondly, it ties in nicely with an approach to testing that involves writing tests as code alongside the ‘real’ code. This approach is sometimes referred to as Test-Driven Development (TDD), and is popular with the ‘Agile process’ developers. I’ll return to the testing issue in later posts.

Now, you might be familiar with the use of classes for dynamic data structures, such as a Linked List. Here the object is a chain of ListItem objects, each of which holds a value of some type. The class defines operations to add, remove and retrieve values, and possibly others as well (turn the list into a single string, reverse the order, sort it, etc).

The class is represented as a module, but distinguished in the Project Explorer as being a Class Module. This means that you can instantiate objects of this type, as required:

Sub TestList()
Dim li As New List
li.Add (3)
Call li.SetNth(1, 4)
li.Add (6)
End Sub

For more on Lists in particular, there are some pages on MSDN.

However, a List is just a fancy data structure, and could really have been built in to VBA. More interesting are objects that are meaningful in some domain. We’re all more or less familiar with objects in the Excel domain (Worksheet, Range, etc) and the Forms domain (UserForm, CommandButton, etc). What custom classes allow us to do is have objects in our ‘business’ domain (i.e. what our data is about).

Why might we want to do this? Well, the data on our Worksheets will structured in some particular way, to facilitate data entry, or certain types of calculation. However, this particular structuring might make other types of calculation or analysis extremely awkward. What custom objects can do is provide flexible ‘adapters’ over our data, so that we can view and analyse it in different ways.

What I want to do over the next few posts is explore an example which I hope illustrates this approach.

The example concerns allocating scheduled jobs to a limited set of resources:


Obviously, the fundamental constraint is that a resource can’t be double-booked.

Now, suppose that we want to add a new job, with specified start and finish dates. We need to find out which resource(s), if any, can be allocated to it. With the job data in its current form, this is all but impossible to do by formula – even array formulas. (There’s a challenge for you: please let me know if you have a solution!). The problem is that the allocation information for a particular Resource is spread over multiple Job records.

The solution is to build Resource objects each of which amalgamates its own allocation dates (starts and finishes). A Resource object can then answer queries on its availability between two given dates – just as a human resource would consult their diary.

Rather than do this globally, we’ll have Job objects that query Resource objects in turn, until they find one that’s available. So from the user’s point of view, the resource allocation will take place automatically, depending on the specified start and finish dates.

For user interface, we’ll have a form on to a particular Job (either existing or new):


Initially, the form shows a new Job, with the next Id, start and finish dates of today, and an automatically allocated Resource (if one is available). The plus and minus buttons change the dates (with the obvious constraints on date ordering). The OK button writes the Job record to the Worksheet, but only if there is an allocated Resource.

We have, in a very small-scale way, a ‘three layer’ architecture: the form is a View (in the Presentation layer), the Job and Resource objects are Models (in the Application layer) and the Worksheet is our Persistence layer. J2EE beware!

In the next post we’ll look into our Model classes.


6 Responses to “Using Classes – Part 1”

  1. 1 Mangésh Méhendalé July 27, 2011 at 10:16 am

    Hi Roy,

    Ive been reading and re-reading the 4 part blog entry on classes. Just so that I can understand it better, I thought I would divide the whole functionality and try to fit the pieces of code into each of these segments, so that it helps me dive further in. Thought I would put down what I see from here to understand if I have missed anything basic. Here goes:

    1. The entire solution has a 2 part functionality. The main being the ability to ascribe a resource to a job which has a specified start and stop date. The reverse functionality returns the job data specific to jobID (sDate, fDate and Resource) for the accepted jobs entered into the worksheet previously

    2. Essentially a new jobID involves parsing the start date and end date to understand if a resource is available without breaks through this entire period. If this is true, the resource is allocated, else the Resource field on the jobform stays blank

    3. The solution also caters to a unique, one-time situation where there are no jobs entered in previously (making this the first run of the resource allocation solution)

    Since the primary use of classes in this example is for the core functionality of assigning a resource to a job, I want to concentrate purely on that to start off (post which I suspect, the other functionality built in would pretty much be self explanatory- or so I’m hoping! 😉

    Im trying to isolate pieces of the overall code which represent the core functionality in aggregate. But it would be terrific if you could point me in the right direction….


  2. 2 Mangésh Méhendalé July 31, 2011 at 12:57 pm

    Roy – this is an absolutely fascinating piece of code which you have shared!! 🙂 🙂 Appreciate it mate!! 🙂

    It has been a struggle for the previous 5 days to crack it open, but I think I have more or less succeeded in doing that. In the bargain, it has opened my mind (and eyes) to many things including using class modules, linked lists, coding practices, tight and scalable coding as well as extending the function to a reverse lookup functionality. A couple of questions which I am still trying to get my arms around. Would appreciate if you could point me in the right direction.

    1.What is the significance of ‘self referencing’ in the List Item class (Newitem as Listitem). Rather, could not the same functionality served currently by Nextitem be built by introducing a static variable as integer in the class initialise module which would increment by 1 everytime the class object is instantiated. The value of this variable would then be used to SetNth item with the corresponding value that it contains

    2. I notice that at no point after the job class, resource class, list class and listitem class are instantiated that they are programatically destroyed, Is this deliberate or am I missing a trick?

    3. While the isValid code is used through the code wherever it is required, the function invariant is not. So exactly how is it impacting the overall robustness?

    4. Also in most places there are no pre and post checks (the disclaimer here is that I have not got to the test code yet, since I pretty much had my hands full understanding the class modules). Logically it makes sense that the test modules are where these checks should be, but I dint see any coding links passed suggest that the control is being received after pre-checks and is, in turn, ceding to post checks

    5. In the Job ID property Let code, you have dimensioned maxid as integer which does not seem to be used anywhere else in the property Let code? :S (Trivial one and Im assuming it well may be a typo, but Im leaving nothing to chance!)

    Thanks again Roy. This was brilliant!

  3. 3 August 31, 2013 at 12:57 am

    Hello! Do you know if they make any plugins to help with Search
    Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but
    I’m not seeing very good gains. If you know of any please share.

  1. 1 Using Classes - Part 2 « Roy MacLean’s VBA Blog Trackback on March 31, 2009 at 3:46 pm
  2. 2 Testing VBA Code - Part 1 « Roy MacLean’s VBA Blog Trackback on April 20, 2009 at 11:40 am
  3. 3 Testing VBA Code - Part 4 « Roy MacLean’s VBA Blog Trackback on April 29, 2009 at 2:29 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: