The 10 Commandments

I am curious to know from other VBA coders if you had to write the 10 commandments of good VBA programming what would you choose? Here are mine (in no particular order)...

1. Thou shalt declare all variables. NO EXCUSES! Option Explicit is not an option in well written code.
2. Thou shalt give variables, procedures and functions meaningful names. Not everything requires a long drawn out name but short acronyms known only to you just make your code unreadable to others.
3. Thou shalt document code. More is better.
4. Thou shalt put things back the way you found them. If you mess with settings put them back the way you found them. Code should do exactly what it has to do. Nothing more. Nothing Less.
5. Thou shalt keep scope as private as possible. If it can be declared private then it should be declared that way. That applies to procedures, functions and variables.
6. Thou shall not use the error handler as a convenience to account for poorly written code. Avoiding errors is almost always preferable to dealing with them once they occure.
7. Thou shalt give credit where credit is due.If you copy some innovative or ellaborate code from the web or a news group document where it came from.
8. Thou shalt not muck with someone elses work. If you change someone elses code document what part is yours.
9. Thou shalt make it pretty. Appearance counts. Indent your code. Add line breaks where appropriate. Add empty lines and comment lines to section your code.
10. Thou shalt keep it simple. One procedure should do one thing. A function should return a value. Shorter is generally better. Organize our procedures in a modular fashion with simple main procedures calling simple sub procedures. Organize your code into modules that group similar code together. 
11. Thou shalt never use Goto's except as part of the error handler. If you feel like you need to use a goto... stop and think about it. There is always a better way
12. Thou shalt not blame the end user when your code bombs. If it was well written it would handle whatever stupid things the end user has chosen to do.
13. Thou shalt not break rules that you don't understand. The rules are there to keep you out of trouble. Before you break the rules make sure you understand the consequences and account for them. 

I know that that is more than 10 but I did not have to etch mine onto a stone tablet. Just curious what others think and what items they would add to the list.

These are pretty harsh.  I think I've broken all these at one time or another.  I must be going to coder hell.

I have one commandment:  Thou shalt meet all in-scope requirements on time and under budget.

Jim,

Interesting list What I would like to add (i e what comes to my mind):

• Provide robust code solutions (i e avoid flashy solutions). 
• Declare variables based on their scope's level.
• Provide relevant and understandable error messages (when needed) to the end users.
• Create user friendly and flow process's logical UI.
• For larger solutions use helpfile(s) and create system documentation.

If you mess with settings put them back the way you found them.

We all mess with settings. If your change calculation from auto to manual then put it back when you are done. Use error handlers to make darn sure that it gets done. If you mess with settings in your spreadsheet make sure that if I have another spreadsheet open that it has no effect on the other sheet. If you want to remove toolbars from your spreasheet then make sure that if I switch to my spreadsheet that the toolbars come back. Simple stuff like that.

One more commandment.

14. Thou procedures and functions shall have only 1 exit point. Don't put Exit Sub and Exit Function lines here there and everywhere throughout your code. It gets to be darn near impossible to debug procedures that may or may not exit at various points. I am not saying that Exit does not have it's place. I use it at the beginning of procedures and functions all the time to see if I want to execute the main body of code. But once I enter the main body of code I avoid Exit lines. And don't use End Sub. End Sub is almost without exception always a bad idea...

 ...and don't call them the Ten Commandments when there's fourteen. You'll get an overflow error.

More a 'Runtime error 9: Subscript out of range'

A major gripe I have with Microsoft deals with the first commandment.  In all of their help files and all of their examples online, they NEVER delcare variables!  Look at this example from Excel help on the CommandBarButton Object:

Set c = CommandBars("Custom").Controls(2)
With c
If .Type = msoControlButton Then
    If .Style = msoButtonIcon Then
        .Style = msoButtonIconAndCaption
    Else
        .Style = msoButtonIcon
    End If
End If
End With

I can't easily use this code.  What should I DIM "c" as?  (don't get me started on the second commandment).   It gets worse when they'll introduce three or four other objects in the example, without declaring them.

I know, I eventually figure it out, but why can't Microsoft practice what they preach?  In further keeping with your biblical theme, it reminds me of God telling us not to kill, and then smiting all and sundry who tick him off.

Glad to get that off my chest.

Dave

 Just a couple of points. Comments in code are no substitute for proper documentation of what your program does and how it does it. I know that firing up Word and creating some proper documentation is boring, but it needs to be done.

The other important thing to remember about commentary is that it's there to expalin why a particular line of code is there. Commentary is not there to explain what's happening: The clarity of BASIC and your coding style should be able to make it quite clear what each line is doing.

My addition to the commandments would be "Don't hard-code strings and magic numbers - use constants and enums. This has the double benefit of being more maintainable and making code more readable."

 Cheers

Rob 

Rob,

I'm curious as to what you would consider "proper documentation"?  Do you go to the extent of flowcharting, a long writeup on the logic, or...?

For business projects, I would add another - keep all business logic out of code as far as possible, because it is much harder to maintain and check than if it was in a spreadsheet.

Having seen the generally low level of user skills, which makes it important to stay as simple as possible, I do as much as possible in worksheets, using code for automation and utility functions - but not for business calculations.

Rob Bruce:

Comments in code are no substitute for proper documentation

I used to do proper documentation. No one ever looked at it... Now the only time I do proper documentation is when I am consulting and getting paid by the hour. I still don't do flow charts but I will do up some documentation outlining what the program is intended to do and any limitations it has. Additionally I will add in the expected hardware and software it is intended to run on including any required network connections, ODBC drivers and such. There will be a brief discussion of  error messages that could appear and what the expected cause of those messages would be (including some indication of how to go about fixing any problems). I will include a printed copy of the code and pictures of the screens. That makes for a pretty good sized bundle of paper that makes the end user feel a little more comfortable about paying the bill. Chances are that they will never read it but it makes them feel all warm and fuzzy.

 I wasn't thinking so much of documentation for the client or end user (the most important thing you put in there is a clear and agreed statement of project scope!), but the kind of documentation you're going to need when you come back in a year's time to fix a bug/include some enhancements/create version 2. This doesn't have to be a huge book of block diagrams and call trees, but it should include stuff that doesn't usually make it into the code comments (and even less so into the worksheet logic), such as an overview of why you architected like you did, how your classes come together to create an object model, the various ways that logic and data can be held and flow through your application.

This is the kind of stuff that enables you to hit the ground running when you revisit a project. Often the temptation to completely re-write is not there because the original project is no good, but because you no longer understand it.

BTW, while I'm here, a quick note on code print-outs; anyone who needs to look at the code should be able to look at the code. In the editor. Anyone else: Well, what's the point?

Cheers 

I guess you are a bit more disciplined than I am. I am happy to get the internal code commenting done. And from what I have seen I would be happy if others took the time at all. Do you regularily do all of the documenting that you are discussing?

Rob Bruce:

BTW, while I'm here, a quick note on code print-outs; anyone who needs to look at the code should be able to look at the code. In the editor. Anyone else: Well, what's the point?

Accountants like to get big stacks of paper and bulky manuals before they pay the bill. I guess it makes them feel like they are buying something more substantial.

I would definitely go along with this one. I always add a CleanUp: label just before I my Exit Sub or Exit Function statement (above the error handler) and if I need to exit, I go there. It's also the ideal place to reset any settings you may have changed while your procedure was running.