Can a quick access toolbar button call a macro from a specific project?

Question

The following statements include information from MSDN.

When calling a procedure in VBA, one must make sure that the another module in the project doesn't have a procedure with the same name. If that's the case, you have to specify the module to make sure the right procedure is called, like so.

Sub Main() 
Module1.MyProcedure 
End Sub

Now, if you work with multiple projects that have procedures with the same name, you have to specify not only the module but also the project - even if the modules have unique names.

Sub Main() 
    [MyProject.dotm].[MyModule].Main 
End Sub

I have the following specific situation. In my normal.dotm, I have a procedure main in module mod_x. I created a button in my Quick Access Toolbar to call this procedure directly. It works fine.

In my myTemplate.dotm, there is also a procedure called main in module mod_y. So, when I create a document based on this template, I have access to the macros from this template as well as from normal.dotm.

Unfortunately, when pressing the button in the toolbar now, it invariably calls the main-function within mod_y instead of using the correct main-function from normal.dotm.

Is it possible to change this quick access toolbar button to always call the correct function from normal.dotm and no other?

Answer 1

You could ensure that all applicable projects are referenced by the calling project. This will make it simpler to refer to each projects' objects plus adds the ability to prioritize each project.

The normal.dotm template should already be showing in the Project Browser tree:

      

• Click Tools → References and we should find Normal already listed — and "permanently" checked (enabled).

• Click Browse... and change the file type drop-down to Word Documents, to explicitly reference one or more projects from external documents.

  

We can set the also set the priority of each project we add with the Up/Down arrows. This specifies the order in which VBA checks the references when seeking a named procedure. Note that we cannot move a .DOCX project to be "above" the Normal template...

However, we'll have a workaround for that in a moment, necessary so we can refer to the external procedure in question [from our toolbar] without explicitly calling it.

---

Explicit External Calls

I have 4 subroutines named mySub(): two in separate docx file Module1's, one in Module1 of Normal and one in ThisWorkbook of Normal.

Each of them can be called explicitly:

Project_in_Document1.Module1.mySub
Project_in_Document2.Module1.mySub
Normal.ThisDocument.mySub
Normal.Module1.mySub

---

Workaround: call an external sub as if it's internal

In the case of your toolbar, we'll need it to call a sub, something like:

Project_in_Document2.Module1.mySub

...but we can only refer to it with a local procedure name (which wants to "closest" one -- which is in normal.dot.)

So we cheat, by adding another procedure by the same name, only "closer" -- as a local procedure in the current module:

Sub mySub()
    Project_in_Document2.Module1.mySub
End Sub

Now when we (or a toolbar) calls mySub, Excel is directed to the intended procedure.

^{(Click images to enlarge)}

---

Notes:

We can not references any Private procedures from an outside location, so:

• They must not be tagged as Private Sub (or Private Function), and,
• There must not be a module-level declaration of Option Private.

---

Once all the relevant projects are referenced in the References dialog, there's an added bonus of inline definition tips as reminders of the correct syntax:

^{I suppose it would be a better example if I'd used a project that's not built-in... but you get the idea!}

---

More Information & Related Links:

• MSDN : References Dialog Box (VBA)
• Bently.com : Call procedure in another [microstation] project
• VBForums : Calling a Sub Procedure in another VBAProject
• MSDN : Troubleshooting Broken References
• Stack Overflow : Re-use (but not show) Core VBA functions (UDFs) across projects
• MSDN : Migrating Word VBA Solutions to Visual Studio Tools for Office
• MS Docs : How to: Customize the Quick Access Toolbar
• MSDN : How to: Use VBA to Add a Custom Command to the Ribbon