With tongue firmly in cheek...
1. All Excel macros fit into one of two types:- quick and dirty, dashed off in 10 minutes to do a one-off job and clean, written with at least half an eye on programming standard and conventions, which are designed to be used, reused and even rolled out to other users.
2. All Excel macros that you rely upon on a daily basis to do your job will fall into the first category.
3. VBA is a write-only language. Any non-trivial macro is only readable for the length of time it takes you to code it. After that efforts to determine how it works will either fail or lead to the conclusion that it can't possibly work. This observation is not negated by examples of the macro working perfectly.
4. Because of this it is invariably easier to add new code to a complex macro than to modify the code which is already there, any macro which has been in use for a sufficiently long time will consist almost entirely of sections of code undoing the undesired effects of other sections of code within the same macro.
5. The shelf-life for a macro is 3 months. If it is unused for this length of time it will fail to work properly. If the problem is investigated it will become clear that the macro could never possibly have worked in first place (see #3)
6. In accordance with the laws of conservation of energy A+B+C+D > E, where A is the length of time it takes you to write a macro, B is the time to test it, C the time to bug-fix it and D the time to rewrite huge sections of it to work with real, messy data, rather than the very specific set of test data you used and where E is how long it would have taken you to just do the damn job manually.
7. All macro coders have a Dr Jekyll & Mr Hyde type personality split and Mr Hyde is the one who writes all of the comment lines. When trying to debug a macro which has been unused for 3+ months you will find helpful comments like 'check z is less than a/more than a+0.5z else double z and add 3a unless x=z and you will wonder who was driving the keyboard when you wrote that.
8. No matter what your religious beliefs, or lack thereof, you know that conditionally exiting a for...next loop is wrong and you will be punished for it.
9. The chances of a section of code being error-free is equal to the number of lines of comments divided by the number of lines of code in that section. Corollary - the least commented section of code is the most likely to go wrong.
10. It can be mathematically proven that there are at least 114 distinct solutions to any Excel problem. This is why, every time you reply to a post here, you can't shake the nagging feeling that in the time it takes you to write your reply other people will have posted the other 113 solutions, all of which will be markedly shorter, more robust and more functional than yours.
Bookmarks