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
