Possible to write clean code in Excel?

[Steve Jorgensen]

Hi all,

I've been an Access programmer for years, and I'm starting to try to clean up
my act code-wise so that all code is easy to read and expresses its intention
clearly and obviously. Now, I'm doing some work at a company that's doing
Excel macros that have evolved into full-blown data processing applications,
and looking at the code, it's a wonder anything works at all. Column
references are in A1 form, or referencing other columns on the same row via
offsets, and all variety of inscrutable things, hard to search/replace on if a
change needs to be made, and hard to determine the intention of to even kow if
they're working as intended, much less actually fix them.

Now, I started working up an example application to try to demonstrate how to
write legible code for Excel. For simple examples, it's no big deal. Define
named constants with the column names of the various columns, define a
constant for the number of rows in the header, get all the code that operates
on one type of sheet into one module, etc. When it gets just a little bit
more complicated than that, though, such as dynamic ranges of cells, multiple
sections with their own headers, etc., all my pretty coding conventions just
don't hold up.

The only solution I've been able to dream up is complex and involves using
some fairly complex (in implementation) new object modules that will be
unfamilliar to any other Excel programmers seeing them for the first time.

Can anyone point me in a better direction?

Thanks,

- Steve J.

 

[Hmmm]

Gidday,

You can put in all the explanations you want but, it just
makes the file so much bigger.

Rather than explain line by line, write a small summary
above each sub routine that explains the actions and what
it does.

Personally, I use "Gumboot" vba. I think no explanation
required.

Many programmers are taught a strict convention and
complicated code can cause many distractions.

Do as I say, not as I do.

Declare all variables.
Use loops where possible.

regards
Hmmm
visit:
http://au.geocities.com/excelmarksway

 

[Stephen Bullen]

Hi Steve,

Now, I started working up an example application to try to demonstrate how to
write legible code for Excel. For simple examples, it's no big deal. Define
named constants with the column names of the various columns, define a
constant for the number of rows in the header, get all the code that operates
on one type of sheet into one module, etc. When it gets just a little bit
more complicated than that, though, such as dynamic ranges of cells, multiple
sections with their own headers, etc., all my pretty coding conventions just
don't hold up.

When referencing areas of the spreadsheet, it's common practice to use defined
names in the sheet to point to the areas you're interested in, then use those
defined names in the code (i.e. as opposed to constants that refer to columns).
The advantage is that the definition of the name is done within the same
context that it refers to (i.e. the worksheet) and moves as columns are
inserted etc.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie