I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Excel VBA Do's

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live." [Martin Golding]

I am really getting tired of cleaning up other programmers' code. But sometimes this is part of receiving my pay cheques. Please do me and others a favour and apply sound human reasoning as shown below.

Of course this list is meant to put you in good humour. Look at this list as an intellectual challenge and take it with a grain of salt, please.

#

Apply this

Why?

1

Be a good programmer

The most important thing about a good VBA program is that it is a good programm, and NOT necessarily full of fancy VBA tricks. If you do not know about [associative] arrays and if you have no clue about classes, you have to crawl on the floor and cannot fly the plane

2

Good knowledge of Excel and of VBA

You need to know Excel © and VBA well

3

Document your code as needed

Explain what a third party expert would need to know. Do not write novels about trivialities. Good documentation comes along with the code - not at a later stage. Only information hider or muppets would not comment at all

4

Use OPTION EXPLICIT

Forces you to declare all variables you use. If somebody refuses to use this, get rid of him. Good to know: SHIFT + F2 in the VBA editor will bring you to the declaration of a variable or a constant

5

Test your code properly

Applications of reasonable size require test programs or even regression test suites

6

Optimize your code. A trivial start is to switch off screen updating and to set recalculation to manual at the beginning of your code

I use Charles Williams' FastExcel ©. Another good source is his article. For the trivial start I recommend to use the SystemState class

7

Naming convention

A naming convention lets anybody easily identify the type of a programming object

8

Coding convention

A good coding convention makes your code more readable

9

Enumerate columns

Will make code changes easier - or would you like to change ALL hard coded references to columns right to an inserted one? Another example you can find here

10

Avoid Worksheet Change events

It is evil enough if somebody has created volatile worksheet functions. If you use worksheet change events, it's evil to the power of two. In most cases you slow down calculation and you increase the complexity of your application by hiding dependencies. Would you really need such an example? Normally you can easily avoid a worksheet change event by going for the direct dependency (cell or code). If that is really difficult check whether you can get away with a manually invoked sub where the user has to press a button. BTW: Timers which run Excel code are evil, too

11

Clean up recorded macro code

Of course I record a macro when I have forgotten the corresponding commands. But if you use recorded and uncleaned spaghetti code the first maintenance programmer has to mop it up

Sulprobil   Get it done   Contact   Disclaimer   Download