I'm writing a DLL which talks to Excel via its IDispatch interface. From VBA I pass in a Variant containing Application.Caller from which I draw the IDispatch pointer via .pDispVal.
What I'd like to know is how to query the interface via that IDispatch pointer. I want to set up a connection point container, and from there find a connection point to Excel. The ultimate goal is to tie things to Excel's Calculate event and be able to manipulate the Excel data.
I would recommend Binh Ly's timelessly excellent (Delphi) COM tutorials at http://www.techvanguards.com/ which includes chapters entirely dedicated to IConnectionPoint and related mechanisms.
He also offers a free tool for generating EventSink code.
Still, I have a strong feeling that this shouldn't be necessary at all if all you want to do is react to an event triggered by the Excel Application object. Have you tried simply using the wrapper objects that the Delphi Type Library importer generates for you? You can of course also write your own wrappers.
Then again, I haven't actually written any addins for Excel yet - but I do write addins for Outlook and Word for a living and Excel really shouldn't be much different in this regard.