ModulesA Home for Your Code

A module is your canvas, so to speak. Modules serve to contain related procedures, provide a mechanism for declaring variables shared by all module procedures, and provide you with the ability to hide private procedures from procedures located in other modules. Until you start to understand the subtle reasons for using multiple modules, you may question why you would want to use more than one.

The main reason for using more than one module is to improve the maintainability and reusability of your code. One strategy is to use modules to segment your subroutines and functions by the type of functionality they provide. For example, you may have one module that contains code that interacts with a database, one module that is responsible for formatting data for output, and another module that contains all of the code that handles interaction with the end user. The benefit of segmenting your code in this manner is that if something goes wrong or needs to be modified, it is easy to know where to go to make the modification.

Another reason to use modules is that by doing so you can easily manage the visibility of your subroutines and functions. I'll cover this subject in more detail later in the book. For now, suffice it to say that sometimes you'll write a subroutine based on certain assumptions and this subroutine won't work unless the assumptions are correct. Therefore, you want to ensure that only a handful of other subroutines have the ability to call or execute this one. By using separate modules, you'll be able to create private procedures that can only be called by other procedures in the same module. You can customize the behavior of modules in four ways:

Option Explicit When you use Option Explicit, you must declare every variable in all of the procedures in the module; otherwise a syntax error occurs and the code won't compile. I highly recommend that you use this option as it can eliminate subtle (and not so subtle) bugs in your code— for example, those that occur when you misspell a variable name. If you misspell a variable that you've used previously without Option Explicit turned on, VBA simply gives you a new variable to use, which likely doesn't contain the correct value. You can automatically include this statement in every new module by turning on the Require Variable Declaration option. From the Visual Basic Editor (VBE), select Tools Options and check the Require Variable Declaration setting on the Editor tab.

Option Private Module This option marks the code in the module as private so that it can't be seen by users in the Macros dialog box. It also prevents a module's contents from being referenced by external projects. Subroutines in a private module won't appear in Excel when you choose Tools ^ Macro ^ Macros. They'll still be available to other modules within the same project provided the procedures in the private module weren't individually declared as Private.

Option Compare {Binary|Text|Database} The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. The default text comparison method is Binary. With binary comparison, "AAA" < "aaa". With text comparison, "AAA" = "aaa".

Option Base {0|1} The Option Base statement specifies the first index number of an array. If this option is not specified, it defaults to 0.

To use these options, just enter the option and its setting at the top of the module to which it should apply. For example, to require variable declarations and mark a module as private, you'd enter the following at the top of the module:

Option Explicit Option Private Module

Actually, you can use two types of modules: standard modules and class modules. You'll use class modules to create your own objects later in the book. Until we get to class modules, you can use standard modules for all of the examples.

NOTE Class modules are covered in Chapter 12.

0 0

Post a comment