Wednesday, March 29, 2006

Apology of Excel and Excelagility Part III - Streaming Objects from/to Excel

I hope that some of you are actually reading these posts on Excel.
I know, it's not a very sexy topic, but I would like to convince you that in some contexts, under specific conditions, it can be a real kick-ass environment for truly agile development.
I agree, VBA is now pretty old and it has several limitations.. but less than you would normally think.  In the course of the future posts you might find a few interesting and generally useful tricks as I show you how to get around the lack of implementation inheritance and even do some degree of reflection and functional-style programming.


- 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"). _



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.


Comments: Post a Comment

Links to this post:

Create a Link

<< Home

This page is powered by Blogger. Isn't yours?