Application.Run .. (Argument Passed ByRef)

[Jaafar Tribak]

Hi all,

As you know, when passing an argument ByRef the Callee procedure code can change the value underlying the argument in the calling code as shown in the following example :

Sub Caller1()
    Dim X As Long

    X = 1
    Call Callee(X)
    MsgBox X ' returns 2
 
End Sub

Sub Callee(ByRef arg As Long)
    arg = 2
End Sub

The above MsgBox returns 2 as expected instead of 1 because the Callee procedure changed the value of the passed argument


However, if we use Application.Run, the above mechanism no longer works and the passed argument is not modified despite using ByRef... (In the following example, the MsgBox now returns 1 instead of 2)

Sub Caller2()
    Dim X As Long

    X = 1
    Application.Run "Callee", X
    MsgBox X  ' returns 1

End Sub

Sub Callee(ByRef arg As Long)
    arg = 2
End Sub

Does anybody know if there is a way of making the ByRef work with Application.run ?

 

[RoGKoT]

Hi Everybody... finding this post really helps me with answer of Rory

Moreover I discover that Object.Run supports ByRef parameters but also supports functions...
The Application.Run Only supports ByVal parameters and functions but only if if they are from a DLL(never try on MacOs M1) or XLL(not supported on MacOs)

So, basically this resolve all my circular dependencies when using reference in Excel

Thanks again and delivers the good new !