Call an Excel macro from a different Workbook

Question

I have a workbook HasMacros.xlsm and a workbook HasData.xlsm

In HasMacros.xlsm I add a module named Mod1 with this code only:

Sub testmacro()

   MsgBox ("Test")

End Sub

In HasData.xlsm I add a module named Mod2 with this code only:

Sub testmacro2()

   'XXX

End Sub

Nothing else opened in Excel.

I want to call testmacro from testmacro2 where XXX is written.

This fails with immediate compilation error:

Sub testmacro2()

   testmacro()

End Sub

This fails with compilation error "Sub of function not defined" on execute:

Sub testmacro2()

   Call testmacro

End Sub

This fails with immediate compilation error:

Sub testmacro2()

   Mod1.testmacro()

End Sub

This fails on execute:

Sub testmacro2()

   Call Mod1.testmacro

End Sub

I tried How to call function from another specific workbook in VBA? but I get

Name conflicts with existing module, project, or object library

How do I call a macro in HasMacros.xlsm from VBA code in HasData.xlsm

Answer 1

You can also change the name of the VBA project in Workbook HasMacros.xlsm to something other than VBAProject and then set a reference (Tools - References in the VB Editor) to that project from the HasData.xlsm project. That will then allow you to directly call the other macro without using Run.

Answer 2

Application.Run("'Workbook HasMacros.xlsm'!testmacro")