Roll-over Periodic Workbook – Part 2

In Part 1, I illustrated the idea of a periodic report, that would roll-over Current data to become Previous data in the next report. Now I should say something about the design. Here’s a UML-ish diagram:


The blue boxes represent Class modules, the yellow ones are Excel objects. The dotted arrows show procedure-calls (delegation).

RORange is a class that associates a pair of Ranges: a Previous one (Prev_<name>) and a Current one (Curr_<name>). It’s responsible for:

  • defining the Excel names for the Ranges
  • rolling-over the ranges between two given workbooks (i.e. copying and pasting values).

RORange also defines the consistency condition that needs to hold between the two Ranges: that they are single-area, same size and non-intersecting. The last of these is checked by using the intersection operator (whitespace):

intersectcount = Range(ror_prevrange.Address & " " & _
    IsValid = IsValid And intersectcount = 0

The IsValid function is something we can use in our TestRORange module (see earlier postings about testing). For example:

Sub Run()
    Set rng2 = Range("B1:B2,B4")    'multi-area
    Set ror.CurrRange = rng2
    AssertEqual ror.IsValid, False

Class ROBook acts as an add-on to Workbook. It’s responsible for doing the book-level roll-over:

  • creating a new workbook from the template: Workbooks.Add (xltname)
  • calling RollOver for each RORange
  • incrementing date and count for the new workbook
  • saving the new workbook with the ‘next’ filename.

With ROBook it’s hard to come up with a substantial invariant: all we can really check is that its properties are set, and that each RORange is valid. In a similar vein, the only testing I did was to check that the class initialised alright.

The Setup form has a reference to the collection of RORanges, which it manipulates. It really ought to get these, via the ROBook, from the Workbook. Unfortunately, the Workbook module can’t have Public properties, so I had to put a reference to the ROBook into a Constants module.

The Setup form’s AddCommand_Click handler creates a new RORange, sets its properties and calls DefineNames on it, which creates the Excel definitions.

That’s about it … If you want to try it out, the template is here.


0 Responses to “Roll-over Periodic Workbook – Part 2”

  1. Leave a Comment

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

May 2009
« Apr   Jun »

%d bloggers like this: