Application.Run

This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.

Application.Run(Macro, Arg1, Arg2, Arg3, ..) 

You cannot use named arguments with this method. Arguments must be passed by position.
You must pass all the parameters as separate arguments and not as one single string (for example Application.Run("Macro(Arg1,Arg2,Arg3)")

If the datatype of any of your parameters (or of the returned datatype) is not declared then these items are assumed to have the Variant datatype.
All the arguments are converted to values which means you cannot pass any objects
Any objects passed as arguments to a macro will be converted to a value (by applying the Value property to the object).

mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
 

Calling Procedure in a different file

This can be done in two ways.
1) Create a reference to the other file. To create a reference (Tools > References).
After a reference has been established you can call procedures in other files.
The referenced file does not have to be open and is treated like an object library.
2) Use the Application.Run method and specify the filename and folder path of the other file.

Calling Procedure assigned to a Variable

Sub MainProc() 
Dim sprocedure_name as String 
   sprocedure_name = "MySubroutine1" 
   'sprocedure_name = "MySubroutine2"
   Application.Run sprocedure_name 
End Sub 

Sub MySubroutine1() 
End Sub 

Sub MySubroutine2() 
End Sub 

Calling Function assigned to a Variable (with Arguments)

It is possible to use Application.Run to return a value from a function.
Exactly the same thing can be achieved by using a subroutine and a ByRef argument.

Sub MainProc() 
Dim oApplication As Object 
Dim sfunction_name as String 
Dim arguments(0 To 1) As String 

   arguments(0) = "10" 
   arguments(1) = "RETURN_VALUE" 
   sfunction_name = "MyFunction1" 
   'sfunction_name = "MyFunction2"

   Set oApplication = Application 
   Call oApplication.Run(sfunction_name, arguments) 
   Call Msgbox(arguments(1)) 
End Sub 

Sub MyFunction1(ByRef arguments As Variant) 
   arguments(1) = arguments(0) * 10 
End Sub 

Sub MyFunction2(ByRef arguments As Variant) 
   arguments(1) = arguments(0) * 50 
End Sub 

If you want to call a Function that has a return value you can use the GetMemoryAddress API function.

Public Declare PtrSafe Function CallWindowProc& Lib "user32" Alias "CallWindowProcA" _ 
 (ByVal lpPrevWndFunc&, ByVal hWnd&, ByVal Msg&, ByVal wParam&, ByVal lParam&) 

Private Function GetMemoryAddress(ByVal pFunc As Long) 
  GetMemoryAddress = pFunc 
End Function 

Private Sub MakeAChoice() 
Dim lFact As Long 
Dim lSquare As Long 
Dim lChoice As Long 

  lFact = GetMemoryAddress(AddressOf Module1.Factorial1) 
  lSquare = GetMemoryAddress(AddressOf Module1.Square1) 

  'this allows you to use a variable to decide which function to call
  lChoice = lFact 
  'lChoice = lSquare

  Call MsgBox(CStr(CallWindowProc(lChoice, CLng(10), missing, missing, missing))) End Sub 


Public Function Factorial(ByVal arg1 As Long) As Long Dim inp As Long 
    inp = arg1 
    If (inp <= 0) Then 
        Factorial = 0 
    Else 
        Factorial = inp 
        While inp > 1 
            inp = inp - 1 
            Factorial = Factorial * inp 
        Wend 
    End If 
End Function 

Public Function Factorial1( _ 
    ByVal arg1 As Long, _ 
    ByVal arg2 As Long, _ 
    ByVal arg3 As Long, _ 
    ByVal arg4 As Long) As Long 

    Factorial1 = Factorial(arg1) 
End Function 

Public Function Square1( _ 
    ByVal arg1 As Long, _ 
    ByVal arg2 As Long, _ 
    ByVal arg3 As Long, _ 
    ByVal arg4 As Long) As Long 

    Square1 = arg1 * arg1 
End Function 

Word

Runs a Visual Basic macro.
You can pass up to 30 parameter values to the specified macro.
If you specify the document name, your code can only run macros in documents related to the current context, not just any macro in any document.

Application.Run MacroName:="Normal.Module2.Macro1" 

Application.Run "Normal.Module1.MAIN" 

This assumes the VBA Project has been changed to MyProject. This is rarely changed.

Application.Run "MyProject.MyModule.MyProcedure" 

Application.Run "'My Document.doc'!ThisModule.ThisProcedure" 

Include an example with a folder location as well !!
Although Visual Basic code can call a macro directly (without this method being used), this method is useful when the macro name is stored in a variable (for more information, see the example for this topic).
The following statements are functionally equivalent.

Normal.Module2.Macro1 
Call Normal.Module2.Macro1 
Application.Run MacroName:="Normal.Module2.Macro1"