COM event handler parameters returning values

[wpb]

I've been successfully using COM events with AHK for a while, but I've so far avoided the issue of event handlers that expect values to be returned in the parameter list.

Eg. Excel's workbook event, BeforeClose, defined in VBA as:

Private Sub Workbook_BeforeClose( Cancel As Boolean)

Cancel is False when the event occurs. If the event procedure sets this argument to True, the close operation stops and the workbook is left open.

So the parameter "Cancel" needs to be passed by reference in order for the handler to pass back a value in it. Can you do that in AHK with:

Code: Select all

class Workbook_Events
{
	BeforeClose(ByRef cancel, wkb)
	{
		cancel := True	; Prevent the workbook from closing.
	}
}

I don't think this is touched upon in the ComObjConnect documentation.

Many thanks for any help in advance!

[kon]

This seemed to work when I tested it:
NumPut(-1, ComObjValue(cancel), "Short")
- https://autohotkey.com/board/topic/6984 ... /?p=442260

Edit: Should be "Short" not "Int". - I was apparently looking at the wrong chart, or it was incorrect. I've corrected this post.

[wpb]

Thanks, kon. Is "cancel" in the above the name of the parameter in the function/method?

BeforeClose(ByRef cancel, wkb)

And should it be declared "ByRef", or doesn't it matter?

Again, many thanks for weighing in here...

[kon]

Yes, you can replace this line from the script you posted cancel := True ; Prevent the workbook from closing. with the one I posted above.
I just tested it and it seems to still work if it is not declared ByRef.

[wpb]

That works for me too. Thanks so much, kon, I really appreciate it.

This would be a great thing to add to either the COM tutorial thread on the forum here, or the AHK help file.

[kon]

Thanks wpb. I've added a link to this thread at the end of the MS Office COM Basics tutorial.

In case someone finds this helpful, here's a working script that can be used to test the BeforeClose event. This will prevent Excel or the workbook from closing until you quit the script (Hotkey Ctrl+Esc to quit). It displays a traytip message when you try to quit which shows the previous and current values of "Cancel". It also displays a traytip message showing the other workbook events that are not implemented in the script (when they fire).

Code: Select all

#Persistent
xlApp := ComObjCreate("Excel.Application")                          ; Create an instance of Excel
xlApp.Visible := true                                                        ; Make Excel visible
MyWb := xlApp.Workbooks.Add()                                                    ; Add a workbook
ComObjConnect(MyWb, new Workbook_Events)                      ; Connect to the workbooks's events
return

class Workbook_Events
{
    __Call(Event, Args*)
    {
        static EventList                         ; List of recent events that are not implemented
		if (IsFunc(this[Event]))           ; If there is a method defined for this event, call it
			this[Event].Call(Args)
		else                              ; Else display EventList and add this event to the list
        {
            TrayTip, Event not Implemented
                , % "(Newest first)`n`n" 
                . (EventList := RegExReplace(Event "`n" EventList, "s)([^\R]*\R){5}\K.+"))
        }
    }
    BeforeClose(Cancel, wkb)
    {
        ; Reference: 
        ; https://autohotkey.com/board/topic/69847-com-events-using-byref-parms/?p=442260
        OldValue := NumGet(ComObjValue(Cancel), 0, "Short")    ; Get "cancel" value. Bool=2 bytes
        NumPut(-1, ComObjValue(Cancel), "Short")                 ; Change value. -1 is True in VB
        NewValue := NumGet(ComObjValue(Cancel), 0, "Short")              ; Get new "cancel" value
        TrayTip, Blocked, % "Old value: " OldValue "`nNew value: " NewValue
    }
}

^Esc::ExitApp

[kon]

Correction: A VBA Bool is 2 bytes, so it should be "Short" not "Int". I was apparently looking at the wrong chart, or it was incorrect. I've corrected the above posts.

https://www.google.ca/?gws_rd=ssl#q=size+of+bool+in+vba

[Heezea]

This is really cool, especially how __Call can provide the name of any method that is used on performing an action in Excel.

Is there any way to output the names of the parameters (Args*) so you can add the method into the class once you know which parameters it wants?

Specifically, I was looking at the SheetSelectionChange method, which fires when you change the active cell that's selected. Through trial and error, I found the following to kind of work. It gives values but I was hoping to see that 1 = Range, 2 = Worksheet, 3 = Workbook.

Code: Select all

MsgBox, % "Args Workbook:" Args[3].Name "`nArgs Sheet:" Args[2].Name "`nArgs Range:" Args[1].Address

I was hoping for something like shown below, which I couldn't get to work.

Code: Select all

for k,v in Args {
	MsgBox, % "Key:" k "`nVal:" v ;Would like this to output Range, Worksheet, Workbook.
}

[Datapoint]

Is there any way to output the names of the parameters (Args*) so you can add the method into the class once you know which parameters it wants?

Maybe something like this. I think there are probably still some pieces of info that you would need to look for in the Excel Object Browser or Help, but this seems to provide some useful output.

Code: Select all

xlApp := ComObjCreate("Excel.Application")                          ; Create an instance of Excel
xlApp.Visible := true                                                        ; Make Excel visible
MyWb := xlApp.Workbooks.Add()                                                    ; Add a workbook
ComObjConnect(MyWb, new Workbook_Events)                      ; Connect to the workbooks's events
return

class Workbook_Events
{
    __Call(Event, Args*)
    {
        EventInfo := ""
        for key, val in Args
            EventInfo .= "Arg#: " Key "`nVarType: " Format("{:X}", ComObjType(val)) "`nIName: "ComObjType(val, "Name")
                . "`nIID: " ComObjType(val, "IID") "`nCName: " ComObjType(val, "Class") "`nCLSID: " ComObjType(val, "CLSID") "`n`n"
        MsgBox,, % "Workbook." Event " - " Args[Args.MaxIndex()].Name, % EventInfo
    }
}

^Esc::
try MyWb.Close(0)
xlApp.Quit
ExitApp