ByRef Arguments and the Application.Run Method

The Application.Run method is a versatile mechanism to call a subroutine particularly in another workbook or add-in. Its one documented limitation, if we want to call it that, is that all arguments are passed by value (ByVal). So, how does one pass an argument by reference (ByRef)?

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_method.shtml

Posted in Uncategorized

3 thoughts on “ByRef Arguments and the Application.Run Method

  1. If you use Application.Run to run a Function then you can get a return value from the Function.
    The returned value can be anything: an object (worksheet etc) or a variant containing whatever you like.

  2. @Charles – there’s different syntax for assigning objects vs nonobjects. The former requires SET. So wouldn’t ultimate generality require something like

    Dim x As Variant
    On Error Resume Next
    Set x = Application.Run . . .
    If Err.Number <> 0 Then
    Err.Clear
    x = Application.Run . . .
    End If

    Shame VBA can’t be as accommodating as real scripting languages like Perl, Python, PHP, Ruby, etc.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.