Abstract
“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 common sense as shown below.
A Table of What You Should Apply
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 | For a trivial start I recommend to use the SystemState class. Advanced developers should use profiling |
| 7 | Naming convention | A (external link!) naming convention lets anybody easily identify the type of a programming object |
| 8 | Coding convention | A good (external link!) coding convention makes a program more readable, you can maintain it easier, and it will be more reliable and more efficient |
| 9 | Enumerate columns | Will make code changes easier - or would you like to change ALL hard coded references to columns right to an inserted or deleted one? |
| 10 | 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 |
| 11 | Log the execution of your program | With a logger class you can create an audit trail to show who executed your program when, with which parameters, and how your program went on (without issues, with warnings, or with fatal errors) |