View Full Version : application.run - return value byRef
mg25683
11-30-2019, 01:36 PM
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
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
12-01-2019, 04:37 AM
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
12-01-2019, 09:55 AM
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
12-01-2019, 09:56 AM
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
12-01-2019, 12:58 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.