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.


- or -  

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


V. 0.0.2



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

Comments: Post a Comment

Links to this post:

Create a Link

<< Home

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