Excel 15 VBA fails to sink events from IE on Windows 8.1


I'm in Excel VBA and playing with COM libraries. In the past I have sunk events thrown by HTMLDocument with VBA code (this is play for me!). This no longer seems to work. I have investigated and got plenty of detail to offer. I have drilled into the type libraries with OleView and have annotated the VBA code with what I believe is going on underneath. This might be a bug or it might be something that I've just plain messed up on. Please help.

The objective is to get the code in doc_ondblclick running, i.e. to get the event handler wired up. At the moment although I can get to one IHTMLDocument interface (thanks to StackOverflow answer to another question); I cannot successfully get to the interface that sources the events.

UPDATE:THIS CODE WORKS ON AN OLD LAPTOP RUNNING Windows XP, Excel 2007, IE8! So I think this is a bug! **FURTHER UPDATE: I suspect this technology is too old and has now been abandoned by Microsoft in IE11 onwards http://msdn.microsoft.com/en-us/library/ie/bg182625%28v=vs.85%29.aspx#legacyAPIs. To observe a mutation Microsoft have Mutation observers http://msdn.microsoft.com/en-us/library/ie/dn265034%28v=vs.85%29.aspx

Option Explicit

'* Attribute VB_Name = "DHTMLHandler"

'* Environment:
'*   Windows 8.1 64 bit;
'*   Excel Microsoft Office Home and Student 2013, Version: 15.0.4551.1512

'* Tools->References indicate following libraries checked

'* VBA    : Visual Basic For Applications         {000204EF-0000-0000-C000-000000000046} C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
'* Excel  : Microsoft Excel 15.0 Object Library   {00020813-0000-0000-C000-000000000046} C:\Program Files\Microsoft Office 15\Root\Office15\EXCEL.EXE
'* stdole : OLE Automation                        {00020430-0000-0000-C000-000000000046} C:\Windows\SysWOW64\stdole2.tlb
'* Office : Microsoft Office 15.0 Object Library  {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\MSO.DLL
'* SHDocVw: Microsoft Internet Controls           {EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B} C:\Windows\SysWOW64\ieframe.dll
'* MSHTML : Microsoft HTML Object Library         {3050F1C5-98B5-11CF-BB82-00AA00BDCE0B} C:\Windows\SysWOW64\mshtml.tlb

'* Based on code at http://support.microsoft.com/kb/246247

'* In the MSHTML type library we have these following lines for coclass HTMLDocument, indicating events interfaces (marked with "[source]")
'coclass HTMLDocument {
'        [default] dispinterface DispHTMLDocument;
'        [default, source] dispinterface HTMLDocumentEvents;
'        [source] dispinterface HTMLDocumentEvents2;
'        [source] dispinterface HTMLDocumentEvents3;
'        [source] dispinterface HTMLDocumentEvents4;

'* I'd say the following line looks like it should start sinking events defined by HTMLDocumentEvents because in VBA we can only sink the [default] [source] interface
Dim WithEvents doc As HTMLDocument   '* this is what I want
Dim docNoEvents As IHTMLDocument2    '* I know this works, it QIs successfully but it won't sink events!

Private Function doc_ondblclick() As Boolean
    '* An example of an event being handled (at least that is if I could get the wiring to work)
    Debug.Print "dblclick"
End Function

Public Sub Test()
    '* this method is called from Module1 which simply instantiates this class  :  Dim handler As New DHTMLHandler: handler.Test

    Dim ie As SHDocVw.InternetExplorer
    Set ie = New SHDocVw.InternetExplorer
    ie.Navigate "www.bbc.co.uk/weather"

    ie.Visible = True

    While ie.Busy

    '* following on from example code at http://support.microsoft.com/kb/246247 it can seen the HTMLDocument object is
    '* acquired from a browser level object.  In the SHDocVw type library looking there is only 1 browser interface
    '* defined which exports a Document method and that is IWebBrowser
    'interface IWebBrowser : IDispatch { ...
    '    [id(0x000000cb), propget, helpstring("Returns the active Document automation object, if any.")]
    '    HRESULT Document([out, retval] IDispatch** ppDisp);

    Dim itfWebBrowser As IWebBrowser
    Set itfWebBrowser = ie             '* QueryInterfaces for IWebBrowser
    Set docNoEvents = itfWebBrowser.Document

    Debug.Assert TypeName(ie.Document) = "HTMLDocument" 'Re G Serg

    '* Following lines error for me with "Type Mismatch", if it fails for you please confirm by posting.
    Set doc = docNoEvents

    'Also the following (shorter, simpler version) doesn't work
    'Set doc = ie.Document

End Sub
asked on Stack Overflow May 6, 2014 by S Meaden • edited Sep 5, 2014 by S Meaden

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0