VBA Productivity Tip: Managing Ambiguous Names

Today’s VBA Productivity Tip provides an overview of a little-known—yet simple—technique for managing ambiguous names (duplication) issues in VBA subroutines and functions. By using the dot operator, subroutine and function calls can be targeted to specific modules.

Overview

Generally, it’s advisable to make sure all subroutines and functions (procedures) within a VBA project have unique names. If ambiguous names (duplicate names) exist in a project, the following compile error will appear.

Abiguous name compile error will appear when placing a call to a duplicate named subroutine.

This run-time compile error will appear whenever a call is placed to a procedure with an ambiguous name, whether the duplicate procedure exist within the same module, or different modules.

If ambiguous names exist within the same module, there’s no trick around it — all subroutines and functions within the same module must have unique names. However, there is a trick to managing ambiguous procedure names if they are in different modules. Using the dot operator (also referred to as dot notation), we can point to a procedure within a specific module.

Managing Ambiguity

To explain how to manage ambiguity between different modules, we will set up a simple program that relies on three subroutines, each within their own module. The modules keep their default names, Module1, Module2, and Module3. Module1 will hold a subroutine called Main, which acts as the primary procedure called from the application. Within this subroutine, a call is made to another subroutine called Message_Procedure, which is hosted as two separate procedures within Module2 and Module3. The following is how each module/procedure looks within the VBA editor:

Module1

Running this procedure will activate the ambiguity compile error shown above. This script will attempt to call the Message_Procedure subroutine, but it will be unable to determine if it should call the subroutine in Module2 or Module3. This will stop the procedure from running. A corrected version of this procedure is presented later in this post.

Module2

This simple procedure, called from the Main subroutine, will output the message “Module2”.

Module3

This procedure, called from the Main subroutine, will output the message “Module3”.

Preventing the Ambiguity Error

By making a slight modification to the call statement in the Main subroutine, we can point the call to a specific module.

This procedure will now successfully call both identically named Message_Procedure subroutines in Module1 and Module2. By including the module name, dot operator, then procedure name, we can tell VBA exactly where to look for a specific procedure. Given that VBA is directed to a specific module, it no longer cares if the same name exists in a different module.

Common Situations

With most projects, it’s easy to make sure all subroutines have a unique name. However, as projects grow, there may be a few situations which make maintaining unique names difficult.

  • The project becomes complex with up to a hundred or more subroutines. Your attempts to generate unique procedure names may interfere with your well vetted naming convention guidelines.
  • Your project imports external modules. By chance, there may be duplicate named procedures coming in from the external modules.
  • You plan on importing your own project into other projects at some point in the future. This encounters the same issue mentioned in point 2.

Note of Caution

While pointing to a specific module will help eliminate ambiguity errors, there are a few things to be cautious of.

  • If you rename a module, all calls to procedures within that module will need to be updated. This can potentially be a tedious process if a large project relies heavily on specific procedures.
  • Similarly, if you move an ambiguously named procedure to another module, you will also need to update all calls to that procedure by changing the module name in all impacted Call statements.

Relevant Tutorials and Posts

The following are some relevant tutorials and posts associated with this productivity tip.

Leave a Reply