Wednesday, March 29, 2006
Apology of Excel and Excelagility Part III - Streaming Objects from/to Excel
- or -
How to Shape Excel with VBA into a Legible Expressive Agile Platform
Writing Objects to Excel
We have seen how to write out objects to the Immediate Window, but what about writing them to the Excel sheets? After all we set out to work and to be productive within the Excel environment, didn't we?
All you need to do is to extend your class with a 'toExcel' method.
This code will allow you to write out your person to Excel, horizontally. If you want to write it vertically, then you would have had to swap the row and column numbers in the call to the 'Offset' function within the 'toExcel' method. You could parametrize the method with an optional parameter to choose the direction of the print out.
Reading Objects from Excel
Now that we are able to write out Person objects to excel we would also like to allow our users to edit the excel sheet before loading this data back into VBA.
We have seen how to build a Person programmatically, using a Noun function. We will now see how to assemble a Person object from data found on an Excel sheet.
First of all we need a way to create a new Person from scratch, without setting any of its attributes. Once we have a Person object we can then call a 'fromExcel' method to initialise its attributes.
This could be written as:
However, as we are quite lazy, we would prefer to write something like:
The command 'Person_fromExcel' can be defined as a new Noun that assembles new Person objects from a cell reference.
All that is left to do is to define the 'fromExcel' method.
Unsurprisingly, we can see that it is the almost exact converse of the 'toExcel' method.
Being able to read and write objects from and to excel is invaluable when you are trying to reason on the objects of your problem and you want to be able to edit them while the program is running.