Archive for June, 2009

Parameter Arrays

I’ve been looking for an example of using Parameter Arrays: that is, the ability for a procedure to take an arbitrary number of arguments. Incidentally, is there a limit?

This is most familiar with worksheet functions, such as SUM:


where multiple ranges can be specified in the formula.

If you were implementing an equivalent function, you would use the ParamArray keyword:

Public Function MYSUM(ParamArray inranges() As Variant) _
As Double

where the parameter is an array of Variants (always).

So I was wondering how people use this feature, if at all.

If the procedure with the ParamArray is called from code, then you would expect it to be passed an array or Collection – in other words, the input values are already in some kind of structure.

It makes more sense for a formula function, because the formula-writer can then choose an arbitrary set of inputs, one at a time. Could these be anything other than Ranges?

Anyway, I was trying to think of a multiple-Range function that might be useful, and I thought of a function that takes multiple ranges and combines them all into a single column. Let’s call it COLUNION (Union into a single Column). Rather like TRANSPOSE, you use it in an array function over the target Range.


I5:I13 contain the array formula:


You could use Range names instead, of course. The formula is completely ‘live’, so changes to the input ranges propagate immediately.

The main drawback is that, as with many array formulas, you have to get the size of the target range right. Note also that, as visible in Range B, the cells are retrieved by column, then row (so …,4,6,5,7,…).

Here’s the code:

Public Function COLUNION(ParamArray inranges() As Variant) _
               As Variant
    Dim outvalues() As Variant

    'Work out the size needed for outvalues
    Dim ncells As Long
    Dim i As Integer
    For i = LBound(inranges) To UBound(inranges)
        ncells = ncells + inranges(i).Count
    ReDim outvalues(1 To ncells, 1 To 1) As Variant

    Dim j As Long
    j = 1
    Dim rng As Range
    For i = LBound(inranges) To UBound(inranges)
        Set rng = inranges(i)
        Dim cell As Range
        For Each cell In rng
            outvalues(j, 1) = cell.value
            j = j + 1

    COLUNION = outvalues
End Function

Array Assignment

In various programming languages, including VBA, arrays inhabit a twilight zone between the basic types and the classes.

For example, there is usually a syntax for literal arrays. In VBA this is the function Array(), which returns an array of Variant (since the elements could be of mixed types):

TypeName(Array(1,2,3))     –>  Variant()

However, you have to pass this round as a Variant:

Sub TestLiteral()
    Dim i As Integer
    i = GetNth(2, Array(1, 2, 3, 4, 5))
    MsgBox i
End Sub

Function GetNth(n As Integer, arr As Variant) As Variant
    GetNth = arr(n - 1)      '-1 Because Base 0
End Function

Writing ‘arr() As Variant’ in the function header throws a compiler error.

Here’s another curious feature. Suppose we have a 2-D array: this is effectively an array of 1-D arrays. However, you can’t assign a 1-D array to one of the elements of the 2-D array: compiler error again. Here’s some code:

Sub Test2()

    Dim arr2d(1 To 2, 1 To 3) As Integer
    Dim arr1d(1 To 3) As Integer

    Dim i As Integer
    For i = 1 To 3       'stuff some values into arr1d
        arr1d(i) = i * 2

'    arr2d(1) = arr1d   'throws a compiler error
End Sub

Here’s the error:


However, the left-hand and right-hand sides of the assignment have exactly the same type, Integer(1 To 3), as can be seen in the Locals window:


MSDN says:

This type of assignment works only for dynamic arrays of the same type. The two arrays must both be dynamic arrays, and they must be declared as the exact same type

So I suppose we must conclude that the type of the second level of a 2-D array is not considered to be the same type as an apparently equivalent 1-D array, even though this seems both reasonable and useful. Anyone with knowledge of the language implementation, please comment. Time for a cup of tea, methinks.

Time-Slicing Data

It’s quite common that data consists of a sequence of dated event or transaction records. We’re all familiar with this from bank statements, where a sequence of transactions changes the running balance of our account. For a single object (e.g. account), this is easy enough to do with an extra ‘state’ (e.g. balance) column.

However, what do you do when the sequence contains events for more than one object. The obvious approach is to have a state column for each object:


The state columns filter using an IF function. For example, D3 contains:


Aficionados of array formulas can strut their stuff too:


G3 contains:

{=SUM((Date <= $A3) * (Object = G$2) * Value)}

This single-cell array formula fills down and right nicely. Note that the results are subtly different. The IF formula is row-based, so Row 4 shows  B=0, because the B record is in Row 5. The array formula is date-based, so Row 4 and Row 5 both show B=3 for 3rd June. I prefer the latter behaviour.


  • With Excel pre-2007, the column limit might be a factor for a large set of objects.
  • You have to know your set of objects up front, even though some objects might not appear until some way through the event sequence.
  • The notion of state, and the way that event parameters change the state, is simple and numerical (i.e. adding up a running total).

One way of tackling the last point would be to write a custom VBA function: to replace SUM in the array formula. Such a function would take an array of values and zeros: so in H13 it would take {0;0;3;7;0;9;0;3;-3;0;4}. We would loop through the array, accumulating changes as we go. We could even have a function that takes more than one array, for different event parameters.

An alternative approach would be to use a ‘time-slicing cursor’ which can be moved forwards and backwards through the event list, calculating the set of extant objects and  their states, for the currently selected ‘time slice’. This cursor could then be the model for a Form that displays the current state in some way. Partly as a change from Excel, and partly because some of the functionality is already there, I’m currently experimenting with doing this using a Recordset in Access. More on this anon.

Smart Inter-Workbook Links

In a previous post, I looked at how to support ‘rolling-over’ data from one period to the next. In this case, it was done in a forward direction: from Report N to Report N+1. As this involved creating a new workbook from a template, the non-data content of the workbooks was identical.

Alternatively, you might want backward links from Report N to Report N-1. You could just have inter-workbook formulas, like this:


with the workbook name, and posibly a full path. However, in next month’s report (i.e. July), this will need to be:


or whatever is required by the frequency of the reports.

So I was wondering how you could ‘soft-code’ the links, so that the previous workbook name was generated from:

  • a standard prefix, such as “Report_”
  • the current report’s date
  • a reporting frequency (e.g. ‘m’ for monthly, as in the DateAdd function)
  • a number of periods to go back (e.g. -3 for quarterly).

Having identified and opened the previous workbook, we then look for named ranges in the current workbook whose names have a standard prefix, say “Prev_”. The values of these ranges (single or multiple cells) are then set to be the values of the corresponding ranges in the previous workbook (with a “Curr_” prefix, say):

Report N-1: Curr_Xxx   –> Report N: Prev_Xxx          (for all Xxx)

The whole update process is triggered by Workbook_Open in Report N (with a YesNo MsgBox to make it optional).

Note that the named ranges only ever contain data, not formulas. The linking is entirely ‘soft’, using just the range naming convention.

As usual, there’s an assumption that the workbooks are in the same folder, but we could handle a multi-folder structure, providing that it’s date-based (e.g. year folders).

The difference from the earlier approach is that there are no constraints on the structure of the workbooks. They can be the same or different; some or all of the periodic data can be rolled-over. All that is required is that:

  • the workbooks follow the naming convention
    (in this case <wbprefix>_yyyy_mm_dd.xls)
  • Ranges with corresponding names in the two workbooks have the same size and shape.

If you’re interested, here’s the ThisWorkbook module. When you import it, it comes in as a Class module called ‘ThisWorkbook’; you need to copy and paste the code into the proper ThisWorkbook module. The prefixes and other settings are constants in the module header.

Setting Breakpoints in an Open Event Handler

Suppose you want to handle an Open event: Workbook_Open in Excel or Document_Open in Word. You whack in some code, but it doesn’t quite work first time (a rare occurrence, but your self-esteem is intact).

So you put a breakpoint on the handler, save the workbook, and then … Breakpoints are not saved, so your faulty code runs anyway. Okay, why not just run the code manually from within the VBE? This might suffice, but having opened the document to get into the code, the faulty handler has already run, which might leave things in an ‘unclean’ state (maybe some things have updated, but some haven’t).

Well, I suppose that you could comment out the code, save the workbook, open it, uncomment the code, add a breakpoint, and then run manually. However, there are other events being generated when you open a workbook (Workbook_Activate and Workbook_WindowActivate, at least). If you were handling these as well, it’s possible the Workbook_Open handler might not work in isolation (although Workbook_Open is the first event to occur in the sequence).

One trick that seems to work is to define a dummy form: leave it as UserForm1, with no controls, and ShowModal=True. Then, as the first line of Workbook_Open put UserForm1.Show. When the form is displayed, you can zap over to the VBE and set a breakpoint in Workbook_Open. Go back to Excel, close the form, and then we hit the breakpoint and can debug normally. Any other solutions?

I’ll talk about the facility I was debugging next week. It’s a lighter-weight take on rolling-over data between periodic workbooks, using named ranges.

Dynamic Filtering (using events) – Part 2

In Part 1, we looked at the idea of having worksheets that offer dynamically updated views of some data on a ‘master’ worksheet. Editing events on the master are notified to the various filters, which update their worksheets accordingly. Here’s the overall design:


The arrangement on the left is pretty much as we had it in the multi-form example. Here, the DataCursor (was RecordCursor) needs to handle edit operations. Handling deletions is slightly tricky, as it has to maintain a map of Ids to positions, so that the appropriate row cells can be deleted. As before, the form receives CursorMove events and updates its contents.

The DataCursor raises ItemUpdate and ItemDelete events, which are handled by a FilterCursor. This has a private FilterItem function which tests whether an added/updated record falls into its filtered set; it can also find out whether a deleted Id is in its set. If a change event is relevant to a FilterCursor, it calls its own Add/Change/DeleteItem methods, which in turn call a DataCursor for the filtered worksheet (details omitted in the diagram).

The reason why a FilterCursor is a cursor at all is that it has to perform an initial filtering pass through the Data worksheet. So it needs position and value properties, Move* methods, and a reference to the worksheet. Unlike the DataCursor, access to the worksheet is read-only. Maybe in a full OO language we could have separated the filter and cursor aspects into different classes.

You might think that the filter could use the master DataCursor to get its data. However, the filtering pass would change the DataCursor’s position (from start to finish of the records), which would thus cause the DataForm to update  repeatedly (and very quickly!).

The initial filtering is done afresh on Workbook_Open, clearing out any previously saved filtered data. Other approaches that make use of saved filtered data might be appropriate where the size of the filtered sets is very small in comparison with the size of the data set.

When the FilterCursor initializes itself, it gets the value minimum and maximum from the Filters worksheet:


The yellow cells contain VLOOKUPs into the Filters table. Obviously, this isn’t completely general with regard to filtering criteria. At the other extreme, we could simply hand-code various filtering functions into our FilterCursors, which might be okay if those functions are a fixed part of our application.

If you want to update the min/max values, you need to reset the cursors (that is, re-filter); there’s a command button for this. However, we could pick up Worksheet_Change events for those cells, and do the resetting automatically.

So when might all this be useful? I can envisage a ‘work flow’ scenario, where transactional objects (orders, jobs, etc) are received, added to a master list, and then updated to reflect status changes (waiting, inProgress, completed, suspended, etc). The filter worksheets partition these objects according to status (and maybe date range). Status changes cause objects to ‘migrate’ between the various worksheets. There’s no need to do repeated manual filtering: the filtered sets are always up-to-date.

Dynamic Filtering (using events) – Part 1

Peter Schmedling made an interesting comment on an earlier posting about custom events, in which he suggested linking worksheets using events. Thus one might get a pivot table to update automatically, rather than being manually refreshed. The point of using events is that the the updates are ‘pulled’ by the Views, not ‘pushed’ by the Model (the holder of the master data).

It occurred to me that one application of this would be dynamic filtering of data:

  • a Data worksheet would hold the full set of records
  • one or more Filter worksheets would present filtered views of the Data records, perhaps for an attribute range, such as a date range (the Filtered subsets might or might not be disjoint)
  • a Data form would be used to add, edit or delete data records
  • such changes would be propagated automatically to the filtered views.

Unlike doing a standard Advanced Filter, this would be completely automatic, and incremental (after an initial filtering pass).

Here’s some simple data:


To keep things simple, we’ll go for filtering Value by interval. The mapping of intervals to worksheets can be set up on an auxiliary worksheet:


This specifies that worksheet ‘Filter A’ will hold records with Value between 1 and 300, worksheet ‘Filter B’ will hold records with Value between 301 and 1000 (any > 1000 will appear only on the Data worksheet). The yellow cells are VLOOKUPs of the bounds for a particular filter.

Here’s the data form, which is similar to ones described in earlier postings:


Like the earlier forms, this is based on a ‘data cursor’ object that provides access to the Data worksheet. Both the form and the cursor are a bit more complex, as we have to support additions, edits and deletions, as well as previous/next moves.

So, for example:

  • if I add (id5, 555), it appears on both the Data and Filter B worksheets
  • if I delete (id2, 222), it disappears from both the Data and Filter A worksheets
  • if I change (id3, 333) to (id3, 123), it is updated on Data and migrates from Filter B to Filter A.

I’ll talk about the design in the next posting, but the key points are:

  • the Data Cursor object generates ItemUpdate and ItemDelete events (the former includes additions)
  • both types of event pass a record Id as a parameter
  • these events are received by one or more Filter objects
  • each Filter object decides whether the notification is relevant, and makes any necessary changes to its filtered worksheet.

Application Events

As a footnote to the Custom Events postings, the idea of a wrapper class is also how to handle Application events. The techniques are described on various MSDN pages, but here’s a summary.

Since a VBA project does not by default have a module that can hold handlers for Application events, you need to create a Class module, say AppWrapper. This has a WithEvents reference to the Application, plus whatever handlers are required:

Public WithEvents app As Word.Application

Private Sub App_WindowSelectionChange( _
                                  ByVal Sel As Selection)

This is in a Word template, and allows us to handle any change to the selected text (this was somewhat experimental, so I won’t explain why I wanted to do this).

Now we need to initialize the app reference, probably pretty soon after start-up. This is in the ThisDocument module:

Dim appwrap as New AppWrapper

Private Sub Document_New()
    Set = Word.Application

It’s a bit quirky syntactically that in the Class, “Word.Application” is a type, but in the initialization it’s an object reference.

This feels like a bit of a work-around, somehow. Why couldn’t each VBA project have an Application module, analogous to the ThisDocument/ThisWorkbook module, into which application-event handlers go? Admittedly, the contents of such modules would have to be combined, for all open projects, which could lead to name-clashes. But then there’s always one Active project, which could take precedence. Probably one of those things that’s not worth worrying about overmuch.

June 2009
« May   Jul »


Get every new post delivered to your Inbox.