28 March 2012

Somewhat Object-Oriented Excel

One of the good things about Excel/VBA is that VBA supports classes and object-oriented designs.

The bad thing is that the objects and methods aren't accessible with worksheet cell formulas. That means that if you define a class module, you still need an ordinary module for the functions that will provide an interface between the spreadsheet and the class.

There is a way around this, and here I have to give credit to Sam Savage. One of the really useful ideas he put forward in The Flaw of Averages was to pack large arrays of numbers into an XML string each. These strings could go into spreadsheet cells, and various functions would work with the arrays through the XML.

Every object-oriented language has methods that will turn object state into a string. It's usually called serializing. Serialized objects traverse the internet and in-house networks, moving objects (more precisely their states) from place to place, system to system. (Their bodies stay in place but their spirits travel)

A little bit of O-O is possible in Excel using this principle. Let's say we wanted to treat simple arrays as objects that could be manipulated in a workbook. Instead of columns of numbers filling up worksheets, we can use VBA's support for Comma-Separated Value (CSV) strings with join() and split(). These serialized arrays, aka array instances, can live in one worksheet cell each as a CSV string.

Then all we need is a module with the 'methods' that work on the CSV string 'objects'. The three basic, and relatively obvious methods would be

boolean = isCSV(variant)
string = toCSV(array)
array = fromCSV(string)

Then you'd add methods that do useful things, like add two arrays. The cell formula to get the array sum of two arrays might look like

=toCSV(csvAdd(B3, B4)) 

where csvAdd() checks its arguments with isCSV() to see whether they're arrays or CSV strings and uses fromCSV() to convert the strings into arrays for adding.

There are also some things you can do directly with Excel formulas, like getting the average of an array:

=AVERAGE(fromCSV(B4))

This is a simple application of the principle, but with XML or JSON, the objects could have interesting structures. With VBA user-defined data types internally and serializations externally, in worksheet cells or travelling across the internet, you have a fairly powerfull O-O technique.

2 comments:

  1. This sounds incredibly interesting and vaguely esoteric. I'd love to know more about it, but it looks like something I'd be able to get my students to tackle as a good assignment :)

    ReplyDelete
  2. Hi Rick,

    It's really as simple as it looks. As an assignment it's quite rich once you go past the conversions. The array math functions can be interesting. I can send you the source files for both, if it will help.

    ReplyDelete