Wednesday, March 29, 2006
Apology of Excel and Excelagility Part III - Streaming Objects from/to Excel
Excelagility
- or -
How to Shape Excel with VBA into a Legible Expressive Agile Platform
~
V. 0.0.4
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.
Sub toExcel(startcell As Range)
startcell.Offset(0,0) = Me.Name
startcell.Offset(0,1) = Me.Surname
startcell.Offset(0,2) = Me.DateOfBirth
End Sub
That's it!
Dim a_person As Person
Set a_person = Person("John","Smith",#23/11/1980#)
a_person.toExcel [B3]
a_person.toExcel Workbooks("Book1.xls"). _
Worksheets("Sheet2"). _
Range("D10")
a_person.toExcel Workbooks("Book1.xls"). _
Worksheets("Sheet2"). _
Range("my named cell")
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:
Dim a_person As Person
Set a_person = New Person
a_person.fromExcel [b4]
However, as we are quite lazy, we would prefer to write something like:
Dim a_person As Person
Set a_person = Person_fromExcel [b4]
The command 'Person_fromExcel' can be defined as a new Noun that assembles new Person objects from a cell reference.
Module Nouns
Function Person_fromExcel( _
startcell As Range _
) As Person
Set Person = New Person
Person.fromExcel startcell
End Function
End Module
All that is left to do is to define the 'fromExcel' method.
Sub fromExcel(startcell As Range)
Me.Name = startcell.Offset(0,0)
Me.Surname = startcell.Offset(0,1)
Me.DateOfBirth = startcell.Offset(0,2)
End Sub
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.
