Ten Commandments of Excel VBA Programming

Post date: Dec 18, 2013 1:59:06 PM

• 1. Thall shalt know thy problem. Make sure we understand completely before proceeding

  2. Thall shalt not reinvent the wheel. Research/Google to see if someone else has already solved this.

  3. Thall shalt not reinvent the wheel part deux. Try to solve the problem without VBA first.

  4. Thall shalt not create monolithic idols. Break problems into smaller problems which will become tightly focused functions.

  5. Thall shalt assume things will go wrong. Add error checking (NOT IGNORING) to all routines.

  6. Thall shalt code for reusability. Avoid constants and literals in routines. Pass as parameters or consider externalizing in tables.

  7. Thall shalt code with structure. Doing some things the same way eliminates time wasting decisions – such as where to put declarations, error checking, or comments. I use this structure: Comments, Declarations, Error Initialization, Parameter Checking, Variable Initialization, Process, Error Handling, Exit.

  8. Thall shalt use OPTION EXPLICIT. Declare all variables.

  9. Thall shalt use appropriate variable types. Don’t use variants to avoid thinking.

  10. Thall shalt test. TRY to break our routines before putting them into production. It will save us a lot of embarrassment and our reputations.