VBA函数不返回HTMLElementCollection对象

Question

我试图从一个函数返回一个HTMLElementCollection。但是，函数式方式一切正常，但是当代码返回到调用子时，分配给函数输出“myTable”的变量将显示“<无变量>”。我试过将集合作为scripting.dictionary的一部分传回，但结果相同。

任何帮助表示赞赏。谢谢大家。

Sub updateReports() 
'//Function gathers latest report information and adds to sheets("Reports") 
'//URL 
Dim strURL As String 
    strURL = "http://www.ndbc.noaa.gov/station_page.php?station=62103" 

     Dim myTable As HTMLElementCollection 
     Set myTable = getTable(strURL) 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
     'From here on, "myTable" listed as '<No Variables>'. 
     'HTMLElementCollection not sucessfully returned. 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
End Sub 

Public Function getTable(strURL As String) As Variant 
'//Downloads HTML Table from strURL 
'//Create HTTP Object 
Dim oXMLHTTP As Object 
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 
     oXMLHTTP.Open "GET", strURL, False 
     oXMLHTTP.send 

Dim HTMLDoc As New HTMLDocument 
    If oXMLHTTP.Status = 200 Then 
     HTMLDoc.body.innerHTML = oXMLHTTP.responsetext 
     Set getTable = HTMLDoc.getElementsByTagName("tr")() 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
     'Under "getTable" call stack, getTable shows correct object (HTMLElementCollection) 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
    End If 
End Function 

Answer 1

一些测试后，我想我想通了这是怎么回事...尝试创建主子（updateReports）的HTMLDocument的，并通过ByRef参数把它传递给getTable功能。还要将函数的返回类型更改为HTMLElementCollection。类似的东西：

Sub updateReports() 
'//Function gathers latest report information and adds to sheets("Reports") 
'//URL 
Dim strURL As String 
    strURL = "http://www.ndbc.noaa.gov/station_page.php?station=62103" 

     Dim myTable As HTMLElementCollection 
     Dim HTMLDoc As New HTMLDocument 

     Set myTable = getTable(strURL, HTMLDoc) 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
     'From here on, "myTable" listed as '<No Variables>'. 
     'HTMLElementCollection not sucessfully returned. 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
End Sub 

Public Function getTable(strURL As String, ByRef HTMLDoc As HTMLDocument) As HTMLElementCollection 
'//Downloads HTML Table from strURL 
'//Create HTTP Object 
Dim oXMLHTTP As Object 
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0") 
     oXMLHTTP.Open "GET", strURL, False 
     oXMLHTTP.send 

    If oXMLHTTP.Status = 200 Then 
     HTMLDoc.body.innerHTML = oXMLHTTP.responsetext 
     Set getTable = HTMLDoc.getElementsByTagName("tr")() 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
     'Under "getTable" call stack, getTable shows correct object (HTMLElementCollection) 
     '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
    End If 
End Function 

让我知道这是否适合你！