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 [COLOR=#008000][B]' returns 2[/B][/COLOR]
  
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  [B][COLOR=#008000]' returns 1[/COLOR][/B]

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 ?

 

[pike]

Hi,
where do you application run as this works and returns 2

Option Explicit

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

Sub xx()
Application.Run "Caller1"
End Sub

 

[RoryA]

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

Not that I know of. You have to use a workaround like public variables.

 

[Jaafar Tribak]

Hi,
where do you application run as this works and returns 2

Option Explicit

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

Sub xx()
Application.Run "Caller1"
End Sub

Not that I know of. You have to use a workaround like public variables.

Thank you .. Unfortunately, I really need the variable to be declared inside the Caller routine

I have found this documentation by Tushar Mehta. In it, he shows that by encapsulating the passed variable inside a Class Module as a Class Property one can indeed pass and update the variable value via the Run Method.

The problem with this interesting workaround is that a new Class module needs to be incorporated to the vba project which would add much complexity and require quite a bit of adjusting to the whole structure of my existing larger code.

Another issue with this Class approach is that it relies on modifiying the Instancing property of the Class to Public should the calling and called procedures happen to reside each in a different workbook .. This Instancing modification step cannot be done at runtime thus affecting portability of the code.

Finally, I also have tried this Tushar's approach with object variables and doesn't seem to work

 

[Jaafar Tribak]

Ok, I think I have found a way (at least the tests so far look good) !

The idea is to retrieve the passed variable memory address and pass this address to the Callee routine instead of passing the actual variable.

Once this memory address is passed to the Callee routine, we use the handy CopyMemory API to copy the new variable value thus mimicking the way ByRef works in VB (ie: passing the actual pointer address)

Here is an example:

#If VBA7 Then
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#Else
    Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If


Sub Caller1()
    Dim X As Long

    X = 1
    Call Callee(VarPtr(X))
    MsgBox X [COLOR=#008000]' returns 2[/COLOR]
    
End Sub

Sub Caller2()
    Dim X As Long
    
    X = 1
    Application.Run "Callee", VarPtr(X)
    MsgBox X [COLOR=#008000]' now returns 2[/COLOR]

End Sub


#If VBA7 Then
    Sub Callee(ByVal Ptr As LongPtr)
#Else
    Sub Callee(ByVal Ptr As Long)
#End If

    Dim Modified_Arg As Long
    
    Modified_Arg = 2
    CopyMemory ByVal Ptr, ByVal VarPtr(Modified_Arg), LenB(Modified_Arg)

End Sub

I have also tested this method with String and Object Variables and it seems to work just fine

String variable example:

Sub Caller2()
    Dim X As String
    
    X = "Bob"
    Application.Run "Callee", VarPtr(X)
    MsgBox X [COLOR=#008000]'returns 'Charles'[/COLOR]

End Sub


#If VBA7 Then
    Sub Callee(ByVal Ptr As LongPtr)
#Else
    Sub Callee(ByVal Ptr As Long)
#End If

    Dim Modified_Arg As String
    
    Modified_Arg = "Charles"
    CopyMemory ByVal Ptr, ByVal VarPtr(Modified_Arg), LenB(Modified_Arg)

End Sub

Object variable example:

Sub Caller2()
    Dim X As Object
    
    Set X = ThisWorkbook
    Application.Run "Callee", VarPtr(X)
    MsgBox X.Name [COLOR=#008000]'returns activesheet name instead of the workbook name[/COLOR]

End Sub
  

#If VBA7 Then
    Sub Callee(ByVal Ptr As LongPtr)
#Else
    Sub Callee(ByVal Ptr As Long)
#End If

    Dim Modified_Arg As Object
    
    Set Modified_Arg = ActiveSheet
    CopyMemory ByVal Ptr, ByVal VarPtr(Modified_Arg), LenB(Ptr)

End Sub

 

[Jaafar Tribak]

I wrote the above API code in a 64bit system and worked well... Earlier on, I re-tested the code on a 32bit machine and discovered a bit size mistake in the last argument of the CopyMemory code line

Please, do the following correction so it works well on both 32 and 64 bit platforms:

This line :

[COLOR=#333333]CopyMemory ByVal Ptr, ByVal VarPtr(Modified_Arg), LenB([/COLOR][COLOR=#ff0000][B]Modified_Arg[/B][/COLOR][COLOR=#333333])[/COLOR]

Should be :

[COLOR=#333333]CopyMemory ByVal Ptr, ByVal VarPtr(Modified_Arg), LenB([/COLOR][COLOR=#ff0000][B]Ptr[/B][/COLOR][COLOR=#333333])[/COLOR]

 

[Jaafar Tribak]

Further investigation of this subject has lead me to a discovery that I could only describe as a crazy oddity that I would love to share with you here for the record !

If you simply assign the Application object to an object or variant variable and call the Run Method from the variable then the argument is passed ByRef !!

See this example :

Sub Caller()[COLOR=#008000][B] 
    Dim oXlApp As Object[/B][/COLOR]
    Dim X As Long

   [COLOR=#008000][B] Set oXlApp = Application[/B][/COLOR]
    X = 1
    [B][COLOR=#008000]oXlApp[/COLOR][/B].Run "Callee", X
    MsgBox X  [COLOR=#008000]' [/COLOR][COLOR=#008000]Now returns 2 --- [/COLOR][COLOR=#008000]X was successfully passed ByRef  !![/COLOR]

End Sub

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

Tested this with other types of variables and it works just fine.

No memory copiying or API calls required. Just easy stuff ... I truly have no idea why this works !!

 

[RoryA]

That is truly weird! It works on a Mac too.

 

[Jaafar Tribak]

That is truly weird! It works on a Mac too.

I have also tested it accross two instances of Excel and the argument was again successfully passed ByRef in the other instance .. very useful for dynamic inter-process communication ( specially where the client is a vbscript program)

 

[GTO]

Wow Jaafar; nice find!

I had found Tushar's articles some time back and that was the only way I knew of. How easy! Weird, but still slick!

Mark