Things I have learned about Excel macros

**Andrew-R** · 08-17-2010, 08:36 AM

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.

**Mordred** · 08-17-2010, 09:05 AM

Question about #5; if it worked properly to begin with, how does it become obsolete? Is it because user criteria changes?

**Andrew-R** · 08-17-2010, 09:14 AM

Well the list was meant to be humour (or humor, if you're reading this in American), but, seriously, I find that complex macros are hard to get working after they've sat around for a while because they often require data to be in a certain format, or for some additional workbooks to be open, or a certain directory structure/file to exist.

I'm sure those who take a less slap-dash approach to their coding than I suffer less from this problem.

**romperstomper** · 08-17-2010, 09:46 AM

#6 is very similar to a signature line I saw - something like:
"why spend 2 hours doing something by hand, when you can spend 2 weeks perfecting code to do the same thing?"
#11: The quickest way to get a problem solved is often to post the answer that it absolutely cannot be done.

**Cheeky Charlie** · 08-19-2010, 02:40 PM

#12 Sensibly collecting your code together in a genuinely navigable way is not possible for mere mortals - for this reason you can always enjoy the feeling when writing code that you wrote almost the exact same code a week ago.

re: this

Question about #5; if it worked properly to begin with, how does it become obsolete? Is it because user criteria changes?

#13 According to Davy's Law, you are 94 times more likely to soft-code or reference from source data things which never change and hard-code stuff that changes three times a minute. For this reason, you might as well always write your code with no inbuilt flexibility whatever and just rewrite the whole damn thing if anything changes (see #3, and #5). Your alternative is to take rule #6 and replace E with 4*E and kill yourself. Even if that is the best solution in the long term no-one likes a smart-***.