Application.Run with parameter RRS feed

  • Question

  • I am trying to use the Application.Run to execute a routine from another workbook.

    The routine is called UnProtectSheets and requires a Workbook object argument

    I tried several ways to accomplish this but cannot get it to work with the parameter

    so far I have from within the Excel file I want to execute from...

    Dim wb as Workbook 'Workbook object

    Dim fname as String 'name of other workbook

    set wb = "OtherWorkbook"

    ive tried the following...

    Application.Run "'" & fname & "'!UnProtectSheets," &

    Application.Run "'" & fname & "!UnProtectSheets(" & & ")"

    can any one assist?


    Monday, October 22, 2018 5:31 PM


  • With this code in your calling workbook:

    Sub RunCodeFromOtherWorkbook()
        'Assumes the workbook with the macro code is open

        'If the procedure returns a value, use syntax like
        'Result = Application.Run("'MyFile.xlsm'!MacroName", arg1, arg2)

        'If the procedure doesn't return a value, use
        'Application.Run "'MyFile.xlsm'!MacroName", arg1, arg2

        'For example

        Dim fName As String
        fName = "Macro File.xlsm"  'Name of the file with the code to be run
        Application.Run "'" & fName & "'!UnProtectSheets", ThisWorkbook  'or other workbook object

    End Sub

    Use this code in the workbook being called:

    Sub UnProtectSheets(wb As Workbook)
        Dim ws As Worksheet
        For Each ws In wb.Worksheets
            MsgBox ws.Parent.Name & " " & ws.Name
        Next ws
    End Sub


    Monday, October 22, 2018 8:24 PM