Class Conscious








This is going to be the first in a series of items about Code Generation, automatically
generating your code. I have posted a couple of times previously on this topic,
and here,
but I thought a series of posts would be useful, so here is the first.

The Last One

I remember back in the 80s a UK company built a program
called ‘The Last One’,
its premise being that it was the last computer program because it would write
every program from thereon after. Now, after you have picked yourself up off of
the floor and stopped laughing, think about it somewhat objectively. Remember,
this was in the days of largely batch programs, they started at the beginning,
went through a file of instructions or updates, and reacted to each item
encountered. As such, with some very clear rules, it shouldn’t be too hard to
write a program to read those rules and then process the data files according
to those rules. Of course, it would all soon get messy, the types of rules are
infinite, and the types of data is also infinite, so some severe constraints
would have to be imposed, but wouldn’t a program that can handle multiple
applications, even if those applications have to adapt somewhat to that
program, be beneficial (of course, you could say this is exactly the same as
the SAP paradigm, but please, let’s
not talk about SAP when I am feeling good). Event driven applications add a
whole new, more difficult dimension of course, but still…

Automate It

I am a great fan of code generation, using it frequently,
and always look for further opportunities to use it. Why do I think it is so
good? There are many reasons:

  • maintenance
    is reduced, make a small change and rerun and all code affected by that
    change will be re-generated
  • the
    quality is improved, all changes are re-generated, no need to remember
    what needs changing
  • the
    work is done in the more critical and interesting areas, in analysis and

To me that third point is the most important. Code
generation makes you think about the application requirements and how they should be implemented, something you should do by right, but something that
cannot be avoided or done half-heartedly when using  code generation. As the design is so
critical, and as I find it more interesting than cutting code, this suits me

The greatest exponent of code generation that I am aware of
is Kathleen Dollard.
Her book, Code
Generation in Microsft .NET
, is the definitive work on the subject, a
remarkably comprehensive work that provide end-to-end solutions using
XSLT-based code generation based upon a series of templates. As she says … Code generation will turbo-charge your
development cycles by offering speed, reusability, agility, and consistency

Whilst this is a remarkable piece of work, I think that many
might find it daunting, even off-putting. The solution is comprehensive, but it
is based upon .NET and XML, and dare I say, can even been too purist for the
tyros amongst us. For this reason, I will address code generation from a much
simpler starting point, show how you can use code generation today using Excel,
and build upon it over the next few weeks,

Excel & Code Generation

What I have described so far are full-blown applications,
with their attendant complexity, which might discourage you from going any
further.  But let’s step back a bit, and
think about ways in which we can generate code in small doses. Later items will
go further, but we can show immediate benefits. In these examples, we will use
Excel as the code generating engine. Excel is a superb tool for this, as it is
with so many other things, with the grid for our data input, and VBA to handle
any complex code generating algorithms.

You may not be aware of it, but maybe you are already using
Excel to do code generation. If you ever have a list in Excel, and use formulas
and functions to create some more descriptive text that you cut and paste into
a code module, that is a simple form of code generation.

Let’s take a simple example. I use class modules a lot, and
I find that creating properties is a pain, you need to define a private
variable where the actual value is stored, and then Get and Let properties so
that the property can be read from and written to in the class (that is assuming two
things, your property is read/write, they may not all be so; that you don’t
use Public variables for read/write properties – I don’t, I always declare Get
and Let).

Using code generation, all we need are two pieces of
information, the property name and its data type. If we want to cater for some
properties being read only, we need a third denoting the access mode.

So, we can create a spreadsheet with 3 columns

column A – property name – free format text

column B – data type – Long, Double, String,
Boolean, Range (should suffice for now) – an ideal candidate for a data
validation list

column  C
– access mode – R, W, or RW – again another dv list

It would be wise to add a fourth, column D, derived column,
that takes the property name and replaces any spaces with _, and removes any
special characters, as this will be used in the class.

With a few simple formulae, we can easily generate all 3
elements of the property that the class needs,


=”Private m_”&D2&” As “&D2


“Public Property Get “&$D2&”() As ”
    “&$D2&” =
m_”&$D2&CHAR(10)&”End Property”,

Write Property

“Public Property Let “&$D2&”() As ”
”   m_ “&$D2&” =
“&$D2&CHAR(10)&”End Property”,


“Public Property Let “&$D2&”(ByVal val As ”
”    m_”&$D2&” =
val”&CHAR(10)&”End Property”,


That is all there is to it. Create a list of properties with
their attributes, and Excel generates the class code for you, all you need to
do is cut and paste it into the class module.

Next time, we will show how to use VBA to write back this
data into the class code, after all, one of the primary aims of code generation
is to save us from the boring bits. We will also look at  a slightly more complex generation.


RSS subscribers, this code is attached.

16 thoughts on “Class Conscious

  1. Hi Ken, I only wish I were that organised,  I could save even more time. I do keep a blog folder, maybe that will become it.

  2. Hey Dick, I recall the earlier thread that this one points back at. I made some comment there and we threatened to collabarate. Never happened … yet.

  3. Bob,

    Nice to see that this subject is being discussed. I have also found out that code generation is suberb when working with one or more developers on a project.

    As for being organized I would like to recommend to use my free add-in .NET Co Library.

    Kind regards,

  4. Thanks Dennis. I do have a copy of your addin, and I even recommended it to someone else even though I have yet to try it (there’s confidence for you). I do have another library product (name escapes me), and I use MS’ Code Librarin that came with Excel 2000, but that is somewhat limited.

    Have you read Kathleen’s book?

    BTW, point about multiple developers is an excellent one.

  5. Whilst preparing the second item in this series, I found a bug in the Write property formula, it was creating compilation errors.

    The actual formula should be

    =IF(ISNUMBER(SEARCH(“W”,$C2)),”Public Property Let “&$D2&”(ByVal val As ” &$B2&”)”&CHAR(10)&” m_ “&$D2&” = “&$D2&CHAR(10)&”End Property”,””)

  6. Dennis,

    That book is good, but you are right, it is hard work. As I alluded, it is no book for a code generation beginner because it is so complete, so all-encompassing. That is the aim of my posts, to show that even less skilled coders can use code generation, in small bites, building up into an impressive automated process.

    Thanks for that link, makes good reading.

  7. Hi Bob,

    Very interesting subject.

    If it comes to generating the property Let/Get pairs I find mz tools indispensable. H cunningly hid the function on its toolbar under “Other utilities”. This is the option I use most.

  8. Hi JKP,

    I knew someone would bring that up, so I had my answer pre-prepared :).

    Although there are other ways this can be achieved, such as the function in MZ Tools),  in this tool each property is done one at a time, whereas code generation will generate them all in one fell swoop. Most importantly, this is just an example of the technique, it can be extended to much greater things.

    BTW, on MZ-Tools, although I have it and I often recommend it to others as I feel they will find it useful, I rarely use it myself. The only things I use with any frequency are the template error handler, and the stats.

  9. Hi Bob,

    I don’t use much of it either, but the property let/get alone suffices for me. I also use the close all windows and the proc header/err handler a lot.

    NB: Your home page looks odd on my IE8:

    Normal 0 false false false MicrosoftInternetExplorer4 This is going to be the first in a series of items about Code Generation , automatically generating your code. I have posted a couple of times previously on this topic, here and here , but I thought…

Leave a Reply

Your email address will not be published. Required fields are marked *