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")

Answer 3

I didn't want to have to hardcode the name of the master workbook that contained my subroutine into my slave workbook. I had already used the registry to store key information about my master workbook, and had routines that would extract that information.

When I went to use Application.Run I only found examples which showed a hardcoded string for the slave workbook to access the Master workbook method. I was able to come up with a solution which built the string on the fly.

This solution still hard codes the the Module and Sub name, but you get the general idea and can adapt it to meet your needs.

============

Master Workbook: Master.xlsm  
Modules: GlobalMethods  
Sub: SetThePath(string)  

Slave Workbook: Slave.xlsm  
Modules: Init  
Sub: SetUpMyWorld  

==============

Sub SetUpMyWorld()    ' Defined in the Slave Workbook  
    Dim myMaster as string  
    Dim myMasterWB as Workbook  
    Dim SlaveWB as Workbook  

    Set SlaveWB = ThisWorkbook  

    myMaster = GetMyMasterWBName() ' This is another module in Slave  
    If myMaster <> "" Then  
        Set myMasterWB = Workbooks.Open(myMaster)  
        On Error GoTo Complaint1  
        Dim sInvoke as String  

        sInvoke = Chr(39) & myMaster & Chr(39) & "!GlobalMethods.SetThePath"  
        Application.Run sInvoke, SlaveWB.Path  
        Exit Sub  
    End If       

Complaint1:    
    MsgBox "Could not open your Master workbook: " + myMaster + ". Oops!"  

End Sub  

Answer 4

For me this code works:

Workbooks(1).testmacro

It doesn't autocomplete in code, but works.