Designing Code for Reusability
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Once you've mapped out the overall design for your solution, spend some time thinking about how to structure your code within the solution. There's always more than one way to write the code, and with a little additional effort, you can maximize the reusability of your code. The work you do now to make your code reusable can save you and other developers time in the future, when you can reuse code rather than rewrite it. If you write code with the intention of reusing it in other scenarios, your code may also be easier to maintain because you'll tend to write smaller, more compact procedures that are simpler to modify.
Whenever practical, write procedures that are atomic — that is, each procedure performs one task. Procedures written in this manner are easier to reuse.
Adopt a set of coding standards and stick to them. Chapter 3, "Writing Solid Code," makes suggestions for standardizing your coding practices.
Document your procedures and modules well by adding comments, so that you and other developers can easily figure out later what your code is doing.
Group similar procedures or procedures that are called together in a module. Not only does this help you to organize your code functionally, but it can also enhance your solution's performance. By default, VBA compiles code on demand, meaning that when you run a procedure, VBA compiles that module and the modules containing any procedures that that procedure calls. Compiling fewer modules results in improved performance. (To further improve performance, be sure to save your code in a compiled state.)
Develop a library of procedures that you can reuse. Use a code library tool to store procedures and code snippets so that they are immediately available when you need them. Office 2000 Developer includes such a tool, the Code Librarian, which is an add-in for the Visual Basic Editor.
Create custom objects by encapsulating related code in class modules. You can add properties, methods, and events to custom objects, and organize them into custom hierarchical object models. Once you've built and tested a custom object, you can treat it as a "black box" — you and other programmers can use the object without thinking about the code that it contains. When you do need to maintain the code, you only need to modify it once, within the class module.
Develop interfaces to extend custom objects. An interface provides a basic set of properties, methods, and events to any custom object that implements the interface. By using an interface, you can create sets of closely related, yet distinct, objects that share common code.
If you have Visual Basic, you can create Automation (formerly OLE Automation) servers, which are .dll or .exe files that contain custom objects. The advantage to packaging custom objects in separate .dll or .exe files is that you can call them from any VBA project, just as you would call any Automation server, without having to include the code for the object in your project. For more information, see the documentation for Visual Basic 5.0 and Visual Basic 6.0.
For more information about effective code design, see Chapter 3, "Writing Solid Code," Chapter 7, "Getting the Most Out of Visual Basic for Applications," Chapter 9, "Custom Classes and Objects," and Chapter 10, "The Windows API and Other Dynamic-Link Libraries."