CODEWITHSUNDEEP
vbaexcel
user4369549
user4369549

Reputation:

Excel VBA, Empty Object returned from function

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?

Upvotes: 0

Views: 789

Answers (1)

Mark Moore
Mark Moore

Reputation: 510

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

Upvotes: 1

Related Questions