application.run - return value byRef

[mg25683]

Hi,

i am using event procedure that runs procedure from another wokbook.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As range, Cancel As Boolean)




    Call Application.Run("workbookName.moduleName.someProcedure", Target, Cancel)

     ' cancel = false in this line, even though it is "byRef" and the target procedure changed it to false
End Sub

sub someProcedure(target as range, cancel as boolean)
'bahbah
   cancel = true
end sub

how do i get the [cancel] value as set by the [some procedure] ?

thanks

[SamT]

Call Application.Run("workbookName.moduleName.someProcedure", Target, True)

sub someProcedure(target as range, cancel as boolean)
Dim MyCancel as boolean
MyCancel = Cancel
'blah, blah

'Cancel = true

MsgBox MyCancel
end sub

[Bob Phillips]

Although your called procedure defines the argument as the default byref, when you use Application.Run all arguments are passed as ByVal.

If you want to pass it ByRef, you need to fool it. One way is to create a late bound application object and use that object in the run statement, like so

Sub Testit()
Dim Cancel As Boolean
Dim app As Object

    Set app = Application
    app.Run "'myBook.xlsx'!someProcedure", Range("A1:A2"), Cancel

     ' now cancel = true in this line, as defined in the called procedure
    MsgBox Cancel
End Sub

BTW, I always prefer to define the argument types for clarity

Public Sub someProcedure(ByRef target As Range, ByRef Cancel As Boolean)
'bahbah
   Cancel = True
End Sub

[mg25683]

If you want to pass it ByRef, you need to fool it. One way is to create a late bound application object and use that object in the run statement, like so

thank you good sir, fooling it with late binding works.
My I ask, how dit you come out with this idea?

[mg25683]

Call Application.Run("workbookName.moduleName.someProcedure", Target, True)

sub someProcedure(target as range, cancel as boolean)
Dim MyCancel as boolean
MyCancel = Cancel
'blah, blah

'Cancel = true

MsgBox MyCancel
end sub

i am afraid you didn't really get the question

[Bob Phillips]

thank you good sir, fooling it with late binding works.
My I ask, how dit you come out with this idea?

I have to admit I didn't. I used to accomplish with a convoluted class trick, but I got this from an Excel mate a couple of years back, it is simple, and works a treat.