Passing arguments ByRef using Run

I know what you’re thinking: you can’t do that – at least not without a convoluted workaround like public variables or passing memory locations and copying memory blocks. I thought the same too. As did everyone else, including Microsoft, as far as I can tell. However, the intrepid Jaafar Tribak at MrExcel discovered an insanely simple solution to this problem – just late bind the application object.

Yep, you heard. That’s all it takes.

So, as we know, code like this demonstrates that Run passes everything ByVal no matter what you tell it:

Sub runByVal()
    Dim sTemp As String
   
    sTemp = "Hello"
   
    Application.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
   Debug.Print sTemp
   
    Application.Run "PassByRef", sTemp
   
    ' still unchanged, per documentation as the arg is passed ByVal regardless
   Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

The output of both Debug.Print statements will be “Hello”.

But with a simple change, we can pass the argument ByRef:

Sub runByRef()
    Dim sTemp As String
    Dim o ' just so we can late bind the Application: Variant or Object will work
   
    sTemp = "Hello"
   
    Set o = Application
    o.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
   Debug.Print sTemp
   
    o.Run "PassByRef", sTemp
   
    ' this is now changed! Mind. Blown.
   Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

Now the output will be “Hello” followed by “ByRef”. As far as I can tell it works with all variable types including objects.

I don’t really know why it works here (late binding a class won’t make a property routine work ByRef) but it does and is really easy to implement!