Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Runs a macro or calls a function. 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.
Syntax
expression.Run (Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
expression A variable that represents an Application object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Macro | Optional | Variant | The macro to run. This can be either a string with the macro name, a Range object indicating where the function is, or a register ID for a registered DLL (XLL) function. If a string is used, the string will be evaluated in the context of the active sheet. |
| Arg1–Arg30 | Optional | Variant | An argument that should be passed to the function. |
Return value
Variant
Remarks
You cannot use named arguments with this method. Arguments must be passed by position.
The Run method returns whatever the called macro returns.
Error handling
If the called macro raises a runtime error that it doesn't handle (such as an error raised by the Err.Raise method or a division by zero), the error isn't passed to the procedure that calls Run. Excel displays the runtime error dialog box at that point, even if the calling procedure has an enabled error handler set with On Error GoTo. The dialog box is displayed even when Excel isn't visible, so unattended code stops until someone responds to it. If the user chooses End, all running macros stop, including the calling procedure. When Run is called through Automation, the client then receives a generic error instead of the original error number and description.
Errors that the Run method itself raises, such as the error that occurs when the specified macro can't be found, are passed to the calling procedure and can be handled by its error handler.
As with an ordinary procedure call, an End statement in the called macro stops all running macros, including the calling procedure, so the statement after Run isn't executed. Module-level variables are reset, including those in the workbook that contains the called macro and in the workbook of the calling procedure.
Changes that the called macro makes to a ByRef argument aren't returned to the calling procedure. To report a failure to the caller, handle errors inside the called macro and return a value that indicates success or failure, as shown in the following example.
Example
In this example, the ImportData function in the Tools.xlsm workbook handles its own errors and returns False if it fails. The calling procedure, in another workbook, checks the return value instead of relying on its own error handler. Tools.xlsm must be open when Run is called.
' In Tools.xlsm. Called through Application.Run
Public Function ImportData(ByVal filePath As String) As Boolean
On Error GoTo ErrorHandler
Workbooks.Open filePath
' ... process the data ...
ImportData = True
Exit Function
ErrorHandler:
Debug.Print "ImportData failed: " & Err.Number & " " & Err.Description
ImportData = False
End Function
' In the calling workbook
Sub CallImportData()
If Not Application.Run("'Tools.xlsm'!ImportData", "C:\Data\input.xlsx") Then
MsgBox "The import failed."
End If
End Sub
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.