How to run macros in another workbook

 2009-07-21    VBA programming    0    206

The example macro below shows how you can run macros (procedures and functions) that are stored in another open workbook.

Sub ExamplesRunMacroInAnotherWorkbook()
Dim wb As Workbook, strProcedureName As String, strFunctionName As String
Dim varResult As Variant
    Set wb = Workbooks("MyMacroWorkbookName.xls")
    strProcedureName = "MyMacroProcedureName" '
    strFunctionName = "MyMacroFunctionName"
    ' examples
    Application.Run "WorkbookName.xls!ProcedureName"
    Application.Run "'" & wb.Name & "'!" & strProcedureName
    Application.Run "WorkbookName.xls!ProcedureName", 10, "SomeText" ' two arguments
    Application.Run "'" & wb.Name & "'!" & strProcedureName, 10, "SomeText" ' two arguments
    varResult = Application.Run("WorkbookName.xls!FunctionName", 10, "SomeText") ' two arguments
    varResult = Application.Run("'" & wb.Name & "'!" & strFunctionName, 10, "SomeText") ' two arguments
    Set wb = Nothing
End Sub
If you are going to run macros in an Excel workbook from a VBScript and you have to include any input arguments to the macro, you will probably get some runtime error messages complaining about a type mismatch. This is because all the variables in the VBScript are Variants, and your Excel macros probably needs input variables of another type, like String, Long, Boolean. You can solve this problem by converting the variables you are passing from the VBScript to the Excel macro to the proper variable type like this:
' the variable objXL must contain an Excel Application object
' the variable objWB must contain an Excel Workbook object

objXL.Run "'" & objWB.Name & "'!ProcedureName", CStr(strStringVariable), CLng(lngLongValue), CBool(blnBooleanVariable)

blnResult = objXL.Run("'" & objWB.Name & "'!FunctionName", CStr(strStringVariable), CLng(lngLongValue), CBool(blnBooleanVariable))