Archive for March, 2009

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.

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.

VBA to HTML – test

The formatting of VBA code fragements in a blog like this is an issue. Currently, I’m just putting the code within a <pre> block. Obviously, there’s no colour coding (and the line length’s rather limited). Anyway, I’ve come across a converter from Mr. Excel. It’s intended for Excel VBA code, so it’s installed as an Excel add-in.

Here’s a sample:

Sub GetFilenames()
        ‘pre: exists Worksheets(“list”)
        ‘pre: defined Range “filenames” = $A:$A
        ‘pre: defined Ranges “prefix”, “suffix”
        ‘post: Range “filenames” contains * matching file names

    Dim prefix As String
    Dim suffix As String
    Dim filename As String
    Dim rownum As Integer
    ChDir (ActiveWorkbook.Path)
    prefix = Range(“prefix”).Value
    suffix = Range(“suffix”).Value
    rownum = 1
    filename = Dir(prefix + “*” + suffix)
    Do While filename “”
        Range(“A” & rownum).Value = filename
        rownum = rownum + 1
        filename = Dir
End Sub

Let’s see what that looks like…

Hmmm… looks like it’s turned the double quotes into ‘smart’ quotes (&#8220;&#8221;), which are displayed strangely. Also, it hasn’t escaped the angle brackets in ‘<>’ (not equals). Disappointing.

The add-in provides extra menu options inside the VBA Editor, but there don’t seem to be any Options to set.

Weekly Diary Document

A little while ago, I came across the idea of a PocketMod: a paper organiser which is printed out as a single sheet of A4, and then folded into an 8-page A7 booklet.


There’s a simple utility that converts an 8 x A4 PDF into a 1 x A4 PocketMod sheet (with the correct page orientation for folding). Obviously, the 8 x A4 PDF can be generated from a Word document.

You can design your own PocketMod on the website, but I wanted a simple weekly diary with pages To Do, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday/Sunday, Notes (with the appropriate dates for pages 2-7). So I’ve set up a Word template to generate a series of Heading1s, for a week that you select using a Calendar control. The H1s page break before.

The Calendar form looks like this:


This is just a form with the Calendar control and an OK button (right-click the background of the Toolbox and pick Additional Controls…). The code for the form is simple:

Public Property Get CalDate() As Date
    CalDate = Calendar.Value
End Property

Private Sub OKCommand_Click()
End Sub

Private Sub UserForm_Initialize()
    Calendar.Value = Date
End Sub

In the ThisDocument module, we need to trigger the initialization:

Private Sub Document_New()
End Sub

SetupHeadings is the top-level procedure in general module DiaryDoc. This:

  • gets StartDate, using the date from the Calendar form, and making sure that it’s the Monday of the selected week
  • uses AddPara to create an H1 + Normal paragraph, for each page (pages 2-7 inside a For loop)
  • deletes the first paragraph that was created by default when the document opened.

Here’s StartDate:

Function StartDate() As Date
        'Prompt the user for a calendar date.
        'Return the date of the corresponding Monday
        '(preceding if not exact).

    StartDate = CalendarForm.CalDate

        'StartDate not a Monday: go back to previous Monday
    Do While Weekday(StartDate, vbMonday) <> monday
        StartDate = StartDate - 1

End Function

Here’s AddPara:

Sub AddPara(paratext As String)
        'Adds a H1 paragraph with the given paratext,
        'followed by a Normal paragraph.

    With ActiveDocument.Paragraphs.Last
        .Range.text = paratext
        .Style = wdStyleHeading1
    End With

    ActiveDocument.Paragraphs.Last.Style = wdStyleNormal

End Sub

The full module is available on my website (as is the template).

Although not a diary as such, I’m currently using Evernote: a free online/synchronised notebook application. You can create notes from any web page, email or arbitrary selection, with a single click: neat.

Arrays vs. Ranges

Following on from the previous post, I’ve had a look at the VBA WorksheetFunction methods DSum (as an example of the database functions) and Transpose.

The signature of DSum is:

Function DSum(Arg1 As Range, Arg2, Arg3) As Double

The arguments are the database table, the column to sum and the criteria. This suggests that the criteria might be something other than a Range. However, on investigation, this doesn’t seem to be the case. The function throws a Type Mismatch error of you give it a Range.Value array. Is there something else it could take?

So it seems that you need a criteria range out on a worksheet (you can’t create a new Range object, independent of a worksheet). However, you can still change the Value (array) of the criteria range from within the code. For example:

Function TestDsum() As Integer
        'pre: Range "table" exists
        '   with columns "id" and "value" (numeric)
        'pre: Range "criteria" exists, with 2R, 1C

    Dim criteria As Range
    Dim val As Variant        'value array for criteria
    ReDim val(1 To 2, 1 To 1) As Variant

    Set criteria = Range("criteria")

    val(1, 1) = "id"        'column name
    val(2, 1) = 3           'id = 3;
                            'could be a calculated variable
    criteria.Value = val

    TestDsum = WorksheetFunction.DSum(Range("table"), _
                                "value", criteria)

End Function

However, this is only saving us having a DSUM formula out on a worksheet, so I’m not sure it’s hugely useful.

The WorksheetFunction Transpose is even more disappointing: I can’t get it to do anything. The array function TRANSPOSE (in a worksheet formula) takes a worksheet range and switches rows and columns (e.g. 2R x 3C –> 3R x 2C). It might be useful if we could apply similar transposition to VBA arrays.

The signature is just:

Function Transpose(Arg1)

This is odd, because it doesn’t return anything! This implies that is modifies Arg1, which makes it a sub, not a function. Also, there’s no type declaration for Arg1, implying that it is not restricted to (say) a Range.

However, I can’t get it to do anything with either a Range or an array (such as Range.Value). It’s also not clear how it could modify a Range in situ. If anybody knows what it does, please let me know!

Excel Worksheet Functions

In an earlier posting, I talked about the possible division of labour between VBA code and Excel formulas. An alternative approach is to call Excel worksheet functions directly from your VBA code, without the need for input cells and formula cells on a worksheet.

The Excel VBA library defines a global property WorksheetFunction, which returns the singleton WorksheetFunction object. This object offers methods corresponding to a selection of worksheet functions (about 180). These include the financial functions (PMT, etc), statistical and mathematical functions. There are also the Database functions (DSUM, etc), which could be useful.

Where a function would normally (in a formula) take a range reference or name, it might require a Range object. This could well be constructed using the Range function – e.g. Range(“A2:B6”). Not that you should be using literal range addresses in your VBA code, of course. Either you’ll be using range names, or the address string is going to be computed in some way. The latter offers interesting possibilities.

Where an argument must be a Range object, this is indicated in the function signature (in the Object Browser details): Arg1 As Range. Some functions can take either values or ranges – for example, SUM. Here the arguments show as untyped.

So in our Division of Labour example, we could have called the VLOOKUP within our code, rather than using on-worksheet cells. This would simplify the worksheet structure, and keep the VLOOKUP out of the hands of ordinary users (if we think that’s necessary). On the other hand, there’s a case for making things visible on the worksheet: we can see the last used lookup value, it’s easy to test, and so on.

A couple of things I want to investigate. Firstly, the Database functions are specified as (for example) DSUM(Arg1 As Range, Arg2, Arg3) As Double. This suggests that the Criteria (Arg3) can be an array, rather than a Range. Secondly, the array function Transpose(Arg1) is available, which looks like it could be useful – I’ll try to come up with an example. More in a couple of days.

Generating Hyperlink Lists

Back last year, I was producing some HTML user documentation for a financial system. The pages were grouped into folders by type: functional modules, panels (screens or screen sections), general UI facilities, and so on. The contents of each of the folders grew as content was added. What I wanted was a way to generate an HTML page in each folder that presented a list of hyperlinks to each file in that folder. For example:


The first link points to the file ‘UI Filter Fields.html’. “UI ” is the prefix; “.html” is the suffix.

To generate these hyperlinks, my thoughts turned to Excel’s HYPERLINK function. What I needed was a workbook that would put the ‘prefix *.html’ files into a column, then strip off the prefix and suffix, and then use HYPERLINK to create a column of links. The workbook would then sort the hyperlink column and publish it as a web page. Simple!

First, the Excel side. The main ‘list’ worksheet looks like this:


Column A (range “filenames”) is populated by VBA; columns B and C (range “linknames”) strip off the prefix and suffix (using the SUBSTITUTE function); column D contains =HYPERLINK(filenames,linknames). Columns A to C could be hidden if you wish.

A ‘settings’ worksheet allows prefix and suffix to be set, returns the number of files found, and has the Run button:


Now the VBA. The Run button kicks off the whole process:

Private Sub RunButton_Click()
End Sub

GetFileNames uses the VBA Dir function to return matching file names in sequence. There are put into column A. Notice that Dir is called repeatedly until it returns an empty string.

Sub GetFilenames()
        'pre: exists Worksheets("list")
        'pre: defined Range "filenames" = $A:$A
        'pre: defined Ranges "prefix", "suffix"
        'post: Range "filenames" contains
        '               zero or more matching file names

    Dim prefix As String
    Dim suffix As String
    Dim filename As String
    Dim rownum As Integer

    ChDir (ActiveWorkbook.Path)


    prefix = Range("prefix").Value
    suffix = Range("suffix").Value

    rownum = 1
    filename = Dir(prefix + "*" + suffix)

    Do While filename <> ""
        Range("A" & rownum).Value = filename
        rownum = rownum + 1
        filename = Dir

End Sub

The data region is then selected and sorted ascending on column D (which is textually the same as column C).

Sub Sort()
        'pre: exists Worksheets("list")
        'pre: defined Range "filecount"
        '                 contains =COUNTA(list!$A:$A)
        'post: Worksheets("list") is sorted on column D

    Dim filecount As Integer
    Dim rng As String


    filecount = Range("filecount").Value
    rng = "A1:D" & filecount

    Selection.Sort _
            Key1:=Range("D1"), _
            Order1:=xlAscending, _
            Header:=xlNo, _
            MatchCase:=False, _
End Sub

Finally, the hyperlinks in column D are selected and published as a web page, to a file with a name generated using the prefix and suffix.

Sub Save()
        'pre: exists Worksheets("list")
        'pre: defined Range "filecount"
        '                contains =COUNTA(list!$A:$A)
        'post: exists File .\out prefix List.suffix

    Dim filecount As Integer
    Dim prefix As String
    Dim suffix As String
    Dim rng As String
    Dim title As String
    Dim outfile As String

    filecount = Range("filecount").Value
    rng = "list!D1:D" & filecount

    prefix = Range("prefix").Value
    suffix = Range("suffix").Value
    title = prefix & "List"
    outfile = ".\out " + title + suffix

    ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            filename:=outfile, _
            Sheet:="list", _
            Source:=rng, _
            HtmlType:=xlHtmlStatic, _
            title:=title).Publish True

End Sub

Both the sort and publish operations are the kind of thing that you initially do manually with the Macro recorder on, to get some base code with the right arguments and options.

This works for HTML files, and also ‘.doc’ and ‘.xls’ files; just set the suffix appropriately. In these latter cases the output file is still HTML, but with a ‘.doc’ or’.xls’ suffix; Word and Excel are quite happy to open these. You could, if you wish, have an input suffix and an output suffix, but this isn’t necessary.

I think there’s potential for using VBA plus the HYPERLINK function in other ways, to generate ‘dynamic links’.

The main VBA module is available from my website.

March 2009
« Feb   Apr »