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.