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

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

Answer 2

If I'm understanding the question, yes you can centralize a certain amount of code by moving it into a standard code module. There are probably numerous ways you could do this but one of the better ways is to pass in the form object to your functions. Here's an example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If ValidateContact = False Then Cancel = True
End Sub

'This code goes in a standard code module not associated with any form
Public Function ValidateContact(ByRef frm as Form) as Boolean
    Dim bValid as Boolean
    bValid = True
    If Nz(frm!FirstName, "") = "" Then
        MsgBox "First Name cannot be blank."
        bValid = False
    ElseIf Nz(frm!LastName, "") = "" Then
        MsgBox "Last Name cannot be blank."
        bValid = False
    End If
    ValidateContact = bValid
End Function

Also, if I understand correctly you're question is not at all about using Class Modules/Class Objects, at least not explicitly. (In truth, basically every bit of code you write in Access is making use of a class object, most often implicitly.) Another SO user has already posted a great answer about the benefits of using Class objects in Access so I won't expound much more on that. In truth, standard code modules are basically a Singleton class that doesn't have to be instantiated like a class object does. There are reasons why singleton/standard code modules are preferred, at least for some things, if for no other reason than that it's easier to call functions. However, if you find yourself writing functions that have about four or more arguments, you'd probably benefit from changing it to a class object which would allow you to set properties before calling related methods.

Answer 3

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.