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.
 
 

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.

 


Tuesday, March 28, 2006

Ruby Social Club - 1st April Milan Meeting

The Ruby Social Club is getting up to speed and spreading fast. After the first two meetings in Rome we are going to meet up in Milan on the evening of saturday the first of April.

In the course of the first two meetings I have met very nice easy going people, and very competent brilliant developers too. It's hard to find the right alchemy that allows you to talk offhandedly with newly met people about metaprogramming and semiotics, while drinking good wine and picking at arrosticinos.

The avenue has not been defined yet, but you can keep up to date via our italian Ruby-It wiki page.

If you fancy catching up with us there, get in touch with us and we will make sure there is enough wine and food for you too :-)

The Art of Shaping

Shaping Code After Language is in the air.. and I believe it is the way to go. In these two posts I am trying to capture the spirit of Shal and put it down on paper..


The Way of Meta

The first four and half installments of an exploration of Ruby Metaprogramming


Evolving Intent

My explorations of Intent and how it develops from Test Driven Design


Excelagility - Shaping VBA into an Agile Platform

The first two draft installments of my attempt at showing the power of Shaping Code After Language and redeeming Excel and VBA as an agile platform..

Wednesday, March 08, 2006

Updated Table of Content

I have updated the Liquid Development Table of Contents with the latest posts..


Excelagility - Part II - Printing out objects with external polymorphism

Where we explore how to get rid of some of the verbose bureaucracy of VBA and how to create uniformity using 'Verbs' and external polymorphism..


Excelagility

- or -  

How to Shape Excel with VBA into a Legible Expressive Agile Platform

~

V. 0.0.2

Printing Out Objects

Now we have objects, but it's not easy to print them out to screen, for example for debugging purposes.

We can do this by providing each class which is of interest to us with a method that converts it to string: a 'toString' method.

 

Dim a_person As Person

Set a_person = Person("John","Smith",#23/11/1980#)

 

Debug.Print a_person.toString

 

Let's add such a method to the 'Person' class:

 

Function toString() As String

toString = "Person(" & _

           Me.Name & ", " & _

           Me.Surname & ", " & _

           Me.DateOfBirth & ") "

End Function

 

Now you can easily print out a Person object to the Immediate Window.

However, having to write 'Debug.Print' and 'toString' is quite verbose and we can devise a way to make this more readable.

 

Sub puts(an_entity)

Debug.Print strize(an_entity)

End Sub

 

The new 'puts' command substitutes the lengthy 'Debug.Print' command and it also forces the argument passed to it to be converted to a string.  The actual conversion is performed by the 'strize' (short for string-ize) function.

The code for the 'strize' function employs a technique that I call 'external polymorphism' to handle entities of any type and not only Objects.

 

Function strize(an_entity) As String

If IsObject(an_entity) Then

     strize = an_entity.toString

Else

     strize = CStr(an_entity)

End If

End Function

 

This code now allows you to write much more expressive statements:

 

Dim a_person As Person

Set a_person = Person("John","Smith",#23/11/1980#)

 

puts a_person

puts a_person.DateOfBirth

puts a_person.Name

 

The 'puts' command, however, is not as robust as we would like it to be.  If you pass it an object that does not implement the 'toString' method, then it will fail without providing much of an explanation.  We can make it more robust by adding some simple error handling:

 

Function strize(an_entity) As String

On Error Goto cannot_convert_to_string

     If IsObject(an_entity) Then

          strize = an_entity.toString

     Else

          strize = CStr(an_entity)

     End If

Exit Function

 

cannot_convert_to_string:

     strize = TypeName(an_entity)

End Function

 

If the entity passed cannot be converted to string, then we recover by returning simply the type name of the entity.  Alternatively we may want it to return an error message, using the 'MsgBox' command.




Tuesday, March 07, 2006

Excelagility - Part I

This is the first installment o a serie on how to shape VBA and Excel into a useful agile tool.  I know, it's difficult to believe, but it can be done!

How can I convince you of the usefulness of this well known unholy duo? :-)

For the time being, let it suffice to say that one of the main tenets of liquid development is not just to work with users, but to get users to write code with us.. and one of the best way to get them to write code, to directly express their intent, is to do it in such a way that they don't even realize they are coding.

In the course of these installments I will try to prove this point and to show you at the same time how you can Shape Language to your needs, even if it is VBA.


Excelagility

- or -  

How to Shape Excel with VBA into a Legible Expressive Agile Platform

~

V. 0.0.2

 

Introduction

This booklet will show you how to be agile and effective using Excel with VBA.

 

Defining Classes

When you design a class for an excel addin, keep the class private, unless you need to use it from another module (more on this later).

 

Class Person

Public Name As String

Public Surname As String

Public DateOfBirth As Date

End Class

 

Use public properties, as in:

 

Public Name as String

 

Public properties make the code much simpler to read and can always be privatised later, should the need arise.

 

Assembling Objects with Nouns

Once a class has been defined, objects are easy to create:

 

Dim a_person As Person

Set a_person = New Person

a_person.Name = "John"

a_person.Surname = "Smith"

a_person.DateOfBirth = #11/23/1980#

 

Although objects in VBA can be created with a simple 'New' statement, we prefer to use a somewhat more articulate method.  Since we want to have exact control on how an object gets created, initialised and built, we introduce an intermediate layer that takes care of assembling objects for us.

This layer should allow you to create and initialise objects in a much more readable way:

 

Dim a_person As Person

Set a_person = Person("John","Smith",#11/23/1980#)

 

We name this layer the Nouns layer, and we represent it as a Nouns module present in every excel add-in.

 

Module Nouns

Function Person( _

a_name As String, _

a_surname As String, _

a_date_of_birth As Date _

) As Person

     Dim a_new_person As Person

Set a_new_person = New Person

a_new_person.Name = a_name

a_new_person.Surname = a_surname

a_new_person.DateOfBirth = a_date_of_birth

 

Set Person = a_new_person

End Function

End Module







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