When should I move VBA code from a form module to a separate module?

Question

I have quite a few instances where I start with a basic subform, then I have 3 other subforms which build on it, each adding some different elements to the basic ones. This means that I have a code page for each subform, with lots of repetition. Is it OK to put the common elements into a separate module, and leave only the additional code relevant to each subform on its code page? Are there any performance issues with this?

Answer 1

Regarding the last part of your question, "Are there any performance issues with this?"

There are 2 consequences I'm aware of:

1. form load time
2. memory use

As a hypothetical example, imagine you have a form with a tab control. That tab control contains 10 pages, and each page contains a subform. If those subforms include copies of the same VBA code, that common code must be loaded again for each subform. That takes time and increases memory use.

If you move the common code to a standard module and reference it from your subforms, you need load it only once ... reducing both form load time and memory consumption.

So the net result is that the approach you're considering could make your application more responsive ... which may lead to happier users.

However, even if that approach does not yield a noticeable performance improvement, I would still do it anyway because it means you will need maintain only one copy of the common code.

Answer 2

Short answer, yes, do it, split out the code that is repeated between each of the forms.

Longer answer, but pulling out the code that is repeated into a separate module or class is, in my opinion, the better way to do it.

Classes make your code more manageable and easier to maintain. If you have code that is similar in multiple places, it makes maintenance more difficult because you have to change it in each location. Having it located in one place makes it easier to manage and maintain.

The list of advantages was taken from a Chicago Access Users Group (CAUG) talk - "Class Modules in Access":

Advantages of using classes/objects:
 - let's you create more complex objects than tables or queries provide
   alone
 - using classes within classes let's you restrict function visibility
 - class methods & attributes are more descriptive than module's
   function list alone
 - let's you use Intellisense for more efficient coding
 - no "ambiguous name" errors with multiple copies of the same class
   module
 - can copy class modules without worry of creating ambiguous function
   names
 - static variables are implicit in class objects, and so are easier to
   manage
 - isolating access layers within wrapper classes promote
   portability/maintainability
 - better support for separation of UI/business logic/data access =
   n-tier development
 - promotes modular thinking in analysis, design
 - easier to adapt many publicly-available object models to Access apps
 - prepares you for transition to .NET and other fully object-oriented
   architectures