Excel VBA, Empty Object returned from function

Question

I am trying to return an object that contains all of the <td> elements from a webpage using VBA in excel. This code was working when all in one Sub but now I am trying to break it out into functions to improve readablilty and debugging. The object is correctly created in my function but does not seem to be being returned correctly.

This is the function:

Public Function getTablesFromPage(url As String) As Object
    Dim HTML As Object
    Set HTML = CreateObject("htmlFile")
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .SetAutoLogonPolicy (0)
        .Open "GET", url
        .send
        If .Status = "200" Then
            HTML.body.innerHTML = .responseText
            Set getTablesFromPage = HTML.getElementsByTagName("td")
        Else
            MsgBox "HTTP " & .Status
        End If
    End With
End Function 'at this point a watch on getTablesFromPage shows the correct object

And this is how it is called in my Sub:

Dim TDelements As Object
Set TDelements = getTablesFromPage(url)
'at this point TDelements is an empty object

This is my first time writing any VBA and as I said this all worked when it was all within the same Sub, so it must be something about the way I am returning the object?

Try to put your Dim HTML As Object on the top (out of the function), it must be a global variable in this case. — Matteo NNZ
– Matteo NNZ, Commented Dec 17, 2014 at 9:57

Mark Moore · Accepted Answer · 2014-12-17 09:47:10Z

1

I believe it is because you have declared HTML as an object inside your function, so it is not an inscope object for your main procedure. You either need to declare HTML as a module level variable, or include it as a "by Ref" parameter when calling your procedure

answered Dec 17, 2014 at 9:47

Mark Moore

5104 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4369549 Over a year ago

Passing TDelements ByRef to the function (now a sub) worked. Also setting HTML as a module level variable worked but it feels wrong to declare variables like that.

Collectives™ on Stack Overflow

Excel VBA, Empty Object returned from function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related