I really like when I chance across some low-level technical wizardy to help out when VBA hits its limits ( it is what this blog does best!) Today, on StackOverflow a reward bounty of 500 is being awarded to a cracking answer which uses the ConnectToConnectionPoint Win32 API function call to sink events without using the WithEvents keyword.
So, I came across this StackOverflow Q & A where the questioner is asking how to reduce WithEvent declarations and subs with VBA and ActiveX and the responder provides a solution which uses ConnectToConnectionPoint to acquire events without using WithEvents.
The responder says they found the original code on a Japanese website and indeed I believe they are referring to this from Keiichi Tsunoda: Implementation of the event handling by API : ConnectToConnectionPoint. ConnectToConnectionPoint is defined in shlwapi.h which is part of the Windows Shell API (so it's not part of the original COM runtime API).
Googling a little more and I found a VBFormus post, a Mr Excel post and a GitHub Gist which I have placed in the Links section below.
How significant is this for Excel VBA? I do believe it is already possible to reduce the number of WithEvent declarations by introducing a class and holding an array of instances of those classes. Each class instance would be instantiated with the reference to a ActiveX control acquired using OLEObjects() for a worksheet or Controls() for a UserForm. However, the fact that the implementation of ConnectToConnectionPoint is in the Windows Shell library which is what Windows Desktop and the Windows Explorer use suggests that its use for sinking events from other Windows processes may have a more dramatic potential.
However, Mathieu Guindon who runs the RubberDuck project thinks this is a key technology to solving a glitch that had been an obstacle in implementing MVVM for VBA, here is his blog post Making MVVM Work in VBA Part 2 - Event Propagation
Links
- Microsoft Docs - Win32 apps - ConnectToConnectionPoint function (shlwapi.h)
- Stack Overflow - Reduce WithEvent declarations and subs with VBA and ActiveX
- Keiichi Tsunoda in Japan - http://addinbox.sakura.ne.jp/ - Implementation of the event handling by API : ConnectToConnectionPoint
- GitHub Gist - kumatti1 - Use ConnectToConnectionPoint to fire DocumentComplete
- VBForums - ConnectToConnectionPoint not working for sinking Ribbon CommandBarButton Click Event
- MrExcel Message Board - Single Class and EventHandler for multiple Control types in Userform
- Rubberduck News - Making MVVM Work in VBA Part 2 - Event Propagation
- Google - ConnectToConnectionPoint
No comments:
Post a Comment