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:
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
:
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!
Hi, I only recently started using
.Run
, and found, as often, the documentation somewhat lacking. I was not even aware thatApplication.Run
did not support taking argumentsByRef
. 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 simpleCall
.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 firstMacro:=
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 theApplication.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 aByRef
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 theApplication.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.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 theApplication.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 procedureCall
.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 usedApplication.Run
.Application.Run
works as they say it does, and will not pass an argumentByRef
.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.
Let Ps = "42ByRef"
End Sub
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.
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. 🙂
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.
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 simpleCall
***.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***?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
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?! 🙂
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 🙂
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?
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.
You could start here: http://excelmatters.com/2016/03/10/byval-or-byref-whats-the-difference/