Writing Better VBA/VB6 Code

Writing error-free and maintainable code is hard work - it requires a full time commitment to covering all the details. By following the advise in this section, and using tools available in Total Visual CodeTools, you should be on your way to writing better and more maintainable code.

• Use Option Explicit- By specifying Option Explicit, you tell VBA/VB6 to force you to declare every variable used. This makes it easy to avoid program errors because of misspelled variable names.
• Declare Variables Correctly- Organize your Dim statements to keep them all together within a procedure, or with the declarations section of a module. Keep each Dim statement on its own line.
• Avoid Variants-Because the variant type must be capable of holding any type of data (text, numbers, dates, etc.), it adds storage overhead. Using specific data types for data storage is more efficient than using variants.
• Don't Use Type Declaration Characters- Type declaration characters were used in Basic before explicit syntax for type casting, and they still exist today for backward compatibility. Good VB6/VBA code, however, avoids type declaration characters, which are considered obsolete and hard to understand.
• Use Narrow Variable Scoping- When deciding on the scope of a variable, use the narrowest scope possible.
• Implement Robust Error Handling- Every procedure, no matter how small or insignificant, should have some type of error handling. At the most rudimentary level, there should be an On Error GoTo statement that points to a label.
• Add Line Numbers- The built-in ERL function returns the line number where an error occurred. You can use this function as part of your error handling routine to report exactly where the error occurred which simplifies reproducing and fixing bugs.
• Pay Attention to Program Control- With the exception of On Error commands, these constructs (GoTo, GoSub and Return) have little validity in modern VBA programs. They often lead to messy jumps and logic that are difficult to understand. In general, you should not use GoSub…Return statements, but should instead create a procedure.
• Use Constants to Avoid Hard-coded Values- Values that are hard-coded into program code make that program code difficult to understand and difficult to update. Avoid these problems by replacing hard-coded values with centralized constants or variables, or by placing values in a table.
• Use Variable Naming Conventions- Although the relative merits of different naming conventions can cause heated arguments among developers, the use of naming conventions is accepted as a good programming practice.
• Choose Meaningful Variable Names- For documentation purposes, you want names that are long enough to adequately describe the variable or procedure, but you don't want names that are so long that they make your code unreadable or difficult to type.
• Add Comments- Many times, the purpose of a given piece of code is not readily apparent by reading the code itself. Comments go a long way to providing the information that makes the process of understanding, enhancing, and debugging code much easier.
• Use Standard Indentation- Control structures should be indented to improve readability and reduce programming errors. Choose a tab width setting and stick with it.
• Avoid Single-Line If Constructs- Placing an If condition and its action on the same line leads to code that is difficult to read and maintain.
• Use Select Case Correctly- Avoid putting an action on the same line as a Case statement. Instead, put the action on a separate line. Additionally, you should always use a Case Else clause in your Select Case blocks. Without a Case Else statement, your code will not handle unanticipated values.
• Use Classes- Class modules are a powerful way to encapsulate properties and related methods associated with them. It allows for multiple instancing of the class and simplifies the debugging process, since all related variables and code are in one place.