完美Excel

在http://excelusergroup.org/forums/p/116/276.aspx中的找到的。
对于VBA代码编写者，如果必须说出好的VBA程序的10大戒律，我很想知道会选择什么？下面是我的(没有按照特定的顺序)：

• 应该声明所有的变量。Option Explicit在编写良好的代码中不是一个可选项。
• 应该为变量、过程和函数提供有意义的名称。不是所有方面都需要长的名称，但是仅知道短的缩写将会使代码对他人来说难以阅读。
• 应该有代码的文档说明。越多越好。
• 应该在使用完成后恢复设置。如果混淆了已更改的设置，应该将它们恢复。对于这些，代码应该只执行必须要进行的操作。不多不少最好。
• 应该尽可能的保持范围为私有的。如果能够设置为私有的，那么就应该这样设置。这条规则适用于过程、函数和变量。
• 为了省事而不使用错误处理的代码不会认为是好的代码。避免错误最好的方式，是一旦发现错误就能立即处理它们。
• 应该提供代码的来源。如果从网络中或新闻组中复制了一些具有创新性的或精心制作的代码，应注明其来源。
• 不应该弄乱其他人的工作。如果更改某人的代码，应该注明哪部分是自已的。
• 应该使代码美观。外观醒目、缩进代码、在适当的位置添加断行、在代码断添加空行和注释行。
• 保持简洁。一个过程应该只执行一项操作，一个函数应该只返回一个值。越简短通常越好。在模块中使用常见的简单的主过程组织其他过程，主过程调作简单的子过程。将相似的代码组合在一起放置到模块中。
• 除了作为错误处理的一部分外，决不要使用Goto。如果需要使用Goto，则停下来，考虑一下，总会有更好的方法。
• 当代码崩溃时不要责怪最终用户。如果代码已编写良好，那么会让最终用户选择，而不会发生一些愚蠢的事情。
• 不应该违反不理解的规则。规则能避免陷入麻烦。在不遵守规则之前，确保已理解它们并已经过考虑。

是的，已超过了10条，但我必须写出来。我很好奇您有没有其他的想法，如果有，您也可以在下面写下来。
英文原文:
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. (5)Code should do exactly what it has to do. Nothing more. Nothing Less.
(6)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.
(7)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.
(8)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.
(9)Thou shalt not muck with someone elses work. If you change someone elses code document what part is yours.
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.
回贴：
 1、提供健壮的代码解决方案。
Provide robust code solutions (i e avoid flashy solutions).
2、基于其作用域级别声明变量。
Declare variables based on their scope’s level.
3、需要时，应给最终用户提供相关的且便于理解的错误信息。
Provide relevant and understandable error messages (when needed) to the end users.
4、创建用户友好的且流程处理的逻辑UI。
Create user friendly and flow process’s logical UI.
5、对于大型解决方案，使用帮助文件并创建系统文档。
For larger solutions use helpfile(s) and create system documentation.
6、过程和函数应该只有1个出口。不要在代码中随意放置Exit Sub和Exit Function行。
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…
7、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.”
8、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.
9、From my days writing C I got used to everything being a function. Which is all well and good for C but IMO in VBA Subs do sutff and Functions return values. I try to keep the two separated along those lines. That way I have some idea what is expected to happen even before I look at the code. One pet peave of mine is that functions return values and if you don’t specify the type then the function returns a variant. Perhaps that should be another commandment.
#15 - Thou shalt specify the return types on functions. Even if you intend the function to return a variant, specify it.
Which leads me to my next commandment
#16 - Thou shalt specify the manner in which arguments are passed. The default is that arguments are passed by reference. That being said arguments should be passed by value except where you need to permanently alter the variable(IMO). In either case specify how the argument is being passed so that it is obvious what is intended and to demonstrate that you took the time to evaluate the appropriate way to pass the variable.
10、That would be another of my commandments: Be absolutely explicit - always declare both bounds of your array dimensions.
下面是在http://www.utteraccess.com/forums/printthread.php?Board=24&main=1317632&type=post中发现的：
1) Avoid ELSE GOTO and ELSE RETURN.
2) Each module should do one thing well.
3) Use debugging compilers. (Option Explicit)
4) Avoid multiple exits from loops.
5) 10.0 times 0.1 is hardly ever 1.0.
6) Don’t compare floating point numbers just for equality.
7) Make it right before you make it faster.
Keep it right when you make it faster.
9) Make it clear before you make it faster.
10) Don’t sacrifice clarity for small gains in “efficiency.”
11) Don’t strain to re-use code; reorganize instead.
12) Keep it simple to make it faster.
13) Don’t diddle code to make it faster – find a better algorithm.
And perhaps most importantly…
14) Don’t patch bad code – rewrite it.
不好意思，时间仓促，一部分未译成中文，偷懒了… :）