Passing arguments ByRef using Run

Posted on by

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!

10 thoughts on “Passing arguments ByRef using Run”

  1. Alan Elston says:

    Hi, I only recently started using .Run, and found, as often, the documentation somewhat lacking. I was not even aware that Application.Run did not support taking arguments ByRef. This article may have helped me stop a headache before I got it. Thanks. 🙂
    I still have not definitively understood the main difference between .Run and a simple Call.
    My experimenting has suggested that an important difference is that the Macro:= argument strictly speaking should be given a full string path reference to the macro. Furthermore one finds that using this format will result in the Workbook in which the macro is in being opened, should it not already be so.
    In fact, this latter characteristic is the only reason I personally use it. If this characteristic is not required, I find a Call, or even simply exposing the run Method at the interface is adequate in auto instancing and initiating the running of a code.

    I wonder if I may have a partial answer to how / why the phenomena to which you are referring is resulting in a ByRef apparently “working”:
    Let me assume, for the purposes of the argument, that Application.Run works the way they say it works, and not as you do think. Furthermore, let’s assume that the correct syntax is the full string reference to the macro for the first Macro:= argument. Let’s say this argument sets into action the opening of the File in which the macro is in, if necessary. This will result in effectively initialising all variables “taken” in at the Application.Run line. It is not possible therefore to take an existing variable in at that time. As far Excel is concerned this is Earliest / highest up hierarchy occurrence, a sort of “Early Binding”.

    I have done two versions of your main ByRef attempt code, Sub runByRef().
    Both my codes are, however, only doing the attempt at a ByRef call. I use your Runned / Called routine, Sub passByRef , as well as one of mine which is in a different Workbook to the main Workbook. The main Workbook has all other codes in it.

    In my first code, Sub ApplicationDotRunAndWithExpInterface() , I duplicate your results initially, and have a failed attempt at a ByRef Run / Call in lines 3 and 5.
    Lines 7 and 9 do do “work” ByRef . My explanation there is that the syntax is initially accepted in line 7 as a possibility to build the string which is a prerequisite for the Application.Run Method to work. However, I have inadvertently exposed an interface revealing the Method Run which has auto instanced a code run as in a normal procedure Call. The second part of code line 9 does the same thing.

    Sub ApplicationDotRunAndWithExpInterface() '                                                                                                         excelmatters /2017/04/07/passing-arguments-byref-using-run/   stackoverflow /questions/42908101/run-code-in-worksheets-class-code-module-in-another-workbook/42998339#comment72956125_42908817
    1 Dim sTemp As String, mylong As Long
    2  Let sTemp = "Hello"
    3  Application.Run Macro:="'" & ThisWorkbook.FullName & "'!PassByRef", arg1:=sTemp ' If necessary, this would open the Workbook, as is the case in line 5, which I use in anger in a code working triggered by an event. The code works quicker if the file below is open, as it should be. But if my Wife forgot to open it, the line would open it for her. I try to educate her, but you know what it’s like.. She sends me shopping...
    4  Debug.Print sTemp ' Ctrl+g from VB Editor reveals   Hello   in immeditate window - ByRef did not work
    5  Application.Run Macro:="'H:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\OutlineGrouping\RoryAppRun\NeuProAktuelleMakros.xlsm'!Tabelle11.FrmProTypeInPassByRef", arg1:=sTemp
    6  Debug.Print sTemp ' Reveals   Hello   in immeditate window - ByRef did not work
    7  Application.Run Macro:=Workbooks("NeuProAktuelleMakros.xlsm").Worksheets("FoodsLookUpTable").FrmProTypeInPassByRef(sTemp) ' This will not open the Workbook , NeuProAktuelleMakros.xlsm , if it is closed
    8  Debug.Print sTemp ' Reveals   42ByRef   in immeditate window - ByRef did do work
    9  Let sTemp = "Hello": Let mylong = Workbooks("NeuProAktuelleMakros.xlsm").Worksheets("FoodsLookUpTable").FrmProTypeInPassByRef(sTemp)
    10 Debug.Print sTemp ' Reveals   42ByRef   in immeditate window - ByRef did do work
    End Sub

    Going on to my second code, Sub ohIDoRunRunByRefInLateBindingNot() . Code Lines 3 and 5 may possibly start using the Application.Run Method. Certainly line 5 still will open the Workbook in which the macro is in, should it be closed. ( In the corresponding line in your code , o.Run "PassByRef", sTemp , I am not sure if it gets this far ? ) . In any case I was thinking I may have exposed an interface at a late ish time which has revealed the Method Run which has auto instanced a code run as in a normal procedure Call.

    Sub ohIDoRunRunByRefInLateBindingNot()
    Dim sTemp As String, mylong As Long
    1  Let sTemp = "ohello"
    2 Dim oh As Object
       Set oh = Application
    3  oh.Run Macro:="'" & ThisWorkbook.FullName & "'!PassByRef", arg1:=sTemp ' If necerssary, this would open the Workbook, as is the case in the line 5
    4  Debug.Print sTemp ' Reveals   ByRef   in immeditate window - ByRef did do work
    5  oh.Run Macro:="'H:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\OutlineGrouping\RoryAppRun\NeuProAktuelleMakros.xlsm'!Tabelle11.FrmProTypeInPassByRef", arg1:=sTemp
    6  Debug.Print sTemp ' Reveals   42ByRef   in immeditate window - ByRef did do work
    7  oh.Run Macro:=Workbooks("NeuProAktuelleMakros.xlsm").Worksheets("FoodsLookUpTable").FrmProTypeInPassByRef(sTemp)
    8  Debug.Print sTemp ' Reveals   42ByRef   in immeditate window - ByRef did do work
    9  Let mylong = Workbooks("NeuProAktuelleMakros.xlsm").Worksheets("FoodsLookUpTable").FrmProTypeInPassByRef(sTemp)
    10 Debug.Print sTemp ' Reveals   42ByRef   in immeditate window - ByRef did do work
    End Sub

    So , a suggested answer as to how you have passed arguments ByRef using .Run is that, whilst you may have Late Binded the Application, you haven’t used Application.Run.
    Application.Run works as they say it does, and will not pass an argument ByRef.

    I am not necessarily saying I am correct. I have not been able to find clear documentation yet as to what Application.Run does. I have not seen it stated that it opens a workbook, ( and I have seen it clearly stated that it does not take named arguments.?? ) . So I am a bit puzzled.

    Alan
    ‘_- ———-

    The following is my additional code which is Runned / Called. It is in a different Workbook to that where all the other codes are. The full path and name for that workbook is in the main codes.

    Public Sub FrmProTypeInpassByRef(ByRef Ps As String) ' This is in worksheet with Name "FoodsLookUpTable"  and Code Name  Tabelle11   in workbook with Name  "NeuProAktuelleMakros.xlsm"  at Folder   H:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\OutlineGrouping\RoryAppRun
     Let Ps = "42ByRef"
    End Sub
    Reply
    • romperstomper says:

      I’m afraid you’ve misunderstood what your code is doing. Lines 7 in the first code and 5 in the second code are not actually using Run. You’ve called the routine directly in the Macro argument, and the result (which is empty) is passed to Run as the macro name, and therefore ignored.

      Reply
      • Alan Elston says:

        Hi, Thanks for the Reply.
        I too suggested that line 7 in my first code is not using Application.Run. I also suggested it was calling the routine in the Macro argument. That is why it works ByRef. But I did not know for sure, so thanks for the confirmation.
        I suggested that line 5 in my second code, while it might have somehow used Application.Run initially, ( resulting in the characteristic of the File become opened if it were initially closed ) , it was then in end effect calling the routine . That is why it works ByRef.
        One difference between your code line that works ByRef and my line 5 in my second code is that I am referencing a code in a worksheet code module and you a code in a normal module. So I do see that possibly your argument is that you are using Application.Run in that case. It was just a suggestion that possibly for some reason in actual fact, the routine was run directly , as in a simple call case, hence explain why ByRef appeared to work.
        I do not understand what exactly is going on, I was just commenting and making suggestions. 🙂

        Reply
        • romperstomper says:

          You’re still barking up the wrong tree, I’m afraid. The difference between my code and yours is that I am using Application.Run and you aren’t. It’s not the way I’m using it versus the way you are. And yes, Run is passing ByRef in my second version. There is no other difference between my first code version and my second besides the late binding.

          Reply
  2. Alan Elston says:

    I still haven’t been able to find much documentation on Application Run, in particular specifically for VBA Excel. Can anyone point me in the direction of any? I would in particular like to see something confirming what I found, – that it will open a Workbook.
    I have not found yet either the specific documentation where they claim it only takes arguments by value. I would like to see a good summary of the differences in using Application.Run and a simple Call***.
    In my application, the conclusions from my last experiment suggests I should rather do a check to see if my the workbook with a code in which I wish to run is open or not, then open it, and then do a simple Call. In effect it appeared I may have been doing that anyway, but in a somewhat dubious way.
    My experiments suggested if I use the Application.Run correctly, in particular such that it will then open a Workbook if necessary, then the work a round discussed in this blog is relying on it then not really working further correctly, but rather , as I suggested by relying on a side effect that then causes a normal Call type process to take place.. All a bit dubious…
    Possibly the advantages in using Application.Run lie in some quite different application?
    I have seen many Forum Threads where it is suggested that Application.Run is the thing to use if you want to run a macro in another workbook. ??? – Why not just expose the interface as I did causing an auto instancing, allowing you to effectively use it as an object and apply to that its Property of the code “in it”. Possibly this way of doing it is restricted to a Worksheet code module***?

    Workbooks("ABook.xlsm").Worksheets("ASheetx").AMacroInASheetxCodeModule (AArgumantByRefIfULike)

    Maybe no one noticed that “exposing/ auto instancing” way of doing it. Maybe no one noticed that Application.Run with a full path string argument will open the Workbook where the code is , if it isn’t already. The blogs I have seen up until now, all do a check using standard ways to see if the Workbook is open or not, and if not then they open it using standard ways.
    Alan

    Reply
    • romperstomper says:

      Your comment is somewhat off the topic of this post, which was not intended to be a discussion of Run itself. As I’ve said before, maybe it’s time you got your own blog?! 🙂

      Reply
      • Alan Elston says:

        Sorry, 🙁 . I thought if anyone knew of any good documentation in the Application.Run then it might help clear up what and why and if the thing works as you suggested. As such I thought it was on Topic.
        Won’t post anymore 🙂

        Reply
  3. Colin Legg says:

    That is weird. Really weird. And thanks for sharing it, Rory!

    The only explanation I can think of is that the by value argument passing is being enforced by the compiler. Implementing it that way makes no sense to me (why wouldn’t it be enforced by the Run method itself?), but I can’t think of another reason.

    Perhaps you could tap your MS contacts for some information – I’d be really interested to hear what’s happening here?

    Reply
  4. Yao-Mensah Klogo says:

    I am learning VBA on my own for that reason there are some terms I do not understand. Where can I get materials to read on the topic that is ByRef and ByVal.

    Reply

Leave a Reply to Alan Elston Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.