Late binding UDFs for access across workbooks

Question

I made a UDF in excel which I want to access from all of my excel projects. To do this I put it in a module of PERSONAL.XLSB and invoke with =PERSONAL.XLSB!MYFUNC(Arg1, Arg2...)

However I get the error User-defined type not defined. This is, if I understand correctly (and I may not!), because my project uses early binding and requires a Library reference to Microsoft Internet Controls. Solution according to this site is to use late binding by first declaring all the variables as Objects and then setting them in some way that I'm not entirely sure about

Some code

Here's my code, stripped of everything except for the Dims and Sets that reference the internet controls:

Public Function GOOGLE_COUNT_latebound(searchTerm As String, xRes As Long, yRes As Long, Optional timeout As Long = 10, Optional arrayIndex As Long = 1) As Variant

    Dim objIE As InternetExplorer
    Dim currPage As HTMLDocument
    Dim valueResult As IHTMLElementCollection
    Set objIE = New InternetExplorer
    Set currPage = objIE.document
    Dim myDiv As HTMLDivElement: Set myDiv = currPage.getElementById("fbar")
    Dim elemRect As IHTMLRect: Set elemRect = myDiv.getBoundingClientRect
    Set valueResult = currPage.getElementById("rg_s").getElementsByTagName("IMG")
    objIE.Quit
    
End Function

Now to late bind objIE I simply write

Dim objIE as object
Set objIE = CreateObject("InternetExplorer.Application")'(but why .Application I'm still not sure about?)

And then for all of the HTMLDocument and HTMLDivElement or anything else which is a subset* of objIE: The linked page makes me think I should declare the variable type as a constant (see below)

*(by "subset" I mean set relative to objIE. Like the HTMLDocument is set as a objIE.document or the HTMLDivElement is set as a objIE.document.getElementById("fbar"). See, all relative to the application objIE - I don't know what the general term for these variables would be).

Const HTMLDocument As Long = 0

And then create items as a from my objIE object using the declared type:

Dim currPage As Object
Set currPage = objIE.CreateItem("HTMLDocument")

Is this correct? What is the meaning of the Const declaration, and do I always just declare a number like that or does it change with data type? How do I know what to set it to?

Perhaps there's a better way of getting my UDF publically accessible, maybe this has nothing to do with binding. These are all concepts I'm very new to and am yet to find instructions I can follow!

Dim currPage As Object: Set currPage = objIE.document would still work as a late binding — Jordan
At what line did you got the error? If you have added the references to MS HTML Object Library and to MS Internet Controls in your PERSONAL Workbook, it should work. — Daniel Dušek

h2so4 h2so4 · Accepted Answer · 2017-04-25T10:23:19

here is your code adapted to use late binding

Public Function GOOGLE_COUNT_latebound(searchTerm As String, xRes As Long, yRes As Long, Optional timeout As Long = 10, Optional arrayIndex As Long = 1) As Variant

    Dim objIE As Object
    Dim currPage As Object
    Dim valueResult As Object
    Set objIE = CreateObject("internetexplorer.application")
    Set currPage = objIE.document
    Dim myDiv As Object: Set myDiv = currPage.getElementById("fbar")
    Dim elemRect As Object: Set elemRect = myDiv.getBoundingClientRect
    Set valueResult = currPage.getElementById("rg_s").getElementsByTagName("IMG")
    objIE.Quit

End Function

Late binding UDFs for access across workbooks

Some code

1 Answers