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.
Tuesday, March 28, 2006
Ruby Social Club - 1st April Milan Meeting
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
The Way of Meta
- The Way of Meta - Part I
- The Way of Meta - Part II
- The Way of Meta - Part III
- The Way of Meta - Part IV
Evolving Intent
- Instant Intent
- Intent and Proze I
- Intent and Proze II
- Intents, Stories and Facts
- Storytelling
- Evolving Maru Batsu from Intents I
- Evolving Maru Batsu from Intents II
- Distilling Intent From Descriptions
Excelagility - Shaping VBA into an Agile Platform
Wednesday, March 08, 2006
Updated Table of Content
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
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
