Home » Blogs » Vishesh's blog

Calling macro (with/without parameters) from other workbook

Vishesh's picture
Submitted by Vishesh on 13 May, 2011 - 17:21
Following code runs the macro from other opened workbook. Pass the required parameter in the below code function as required. 
Sub RunMacroFromWbk(wbkTarget As Workbook, strProcOrFunc As String, _
    Optional arg1, _
    Optional arg2, _
    Optional arg3, _
    Optional arg4, _
    Optional arg5)
 
    'This code works for a maximum of 5 parameters
    
    If TypeName(arg5) <> "Error" Then
        Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc, arg1, arg2, arg3, arg4, arg5
        Exit Sub
    End If
    If TypeName(arg4) <> "Error" Then
        Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc, arg1, arg2, arg3, arg4
        Exit Sub
    End If
    If TypeName(arg3) <> "Error" Then
        Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc, arg1, arg2, arg3
        Exit Sub
    End If
    If TypeName(arg2) <> "Error" Then
        Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc, arg1, arg2
        Exit Sub
    End If
    If TypeName(arg1) <> "Error" Then
        Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc, arg1
        Exit Sub
    End If
    Application.Run "'" & wbkTarget.Name & "'!" & strProcOrFunc
End Sub
»