vba - 如何组织和构造多子/功能 VBA 代码以使其实际运行？

Question

如何设置运行/宏来执行以下代码。我已经尝试了很多事情，包括创建一个只单独调用每个 subs 的新模块——没有运气。此项目的输出应该是一个 Excel 工作簿，其中包含工作表(4 行 x 8 列)，每个单元格中都带有解析的 JSON 文本。工作表对应于唯一的 URL。例如，Sheet1 = URL1 数据，Sheet2 = URL2 数据等。

Option Explicit

Function GetJson(ByVal url As String) As Dictionary

With New WinHttpRequest
    .Open "GET", "URL" ''[Should this be a random URL, my list of URLs?] 
    .Send
    Set GetJson = JsonConverter.ParseJson(.ResponseText)
End With

End Function

Sub FillTaxiInfo(data As Dictionary, sheet As Worksheet)
Dim i As Integer, taxi As Dictionary
For i = 0 To UBound(data("prices")) - 1
    Set taxi = data("prices")(i)
    If taxi.Exists("ViewDisplayName") Then
      sheet.Cells(i, 1) = taxi("ViewDisplayName")
      sheet.Cells(i, 2) = taxi("fare")("fareType")
Next i

End Sub

Sub FillMultipleCityInfo(myUrls As Variant, book As Workbook)

Dim i As Integer, data As Dictionary, sheet As Worksheet

For i = 0 To UBound(myUrls) - 1
    Set data = GetJson(myUrls(i))
    Set sheet = book.Sheets(i + 1)
    FillTaxiInfo data, sheet
Next i
End Sub

 Dim myUrls As Variant
myUrls = Array("URL1", "URL2", "URL3", "URL4", "URL5", "URL6", "URL7")
FillMultipleCityInfo myUrls, ActiveWorkbook

Answer 1

将全局变量移到顶部，然后创建一个入口点 sub 初始化它并执行 FillMultipleCityInfo :

Option Explicit

Dim myUrls As Variant

Public Sub RunThis()
    myUrls = Array("URL1", "URL2", "URL3", "URL4", "URL5", "URL6", "URL7")
    FillMultipleCityInfo myUrls, ActiveWorkbook
End Sub

Function GetJson(ByVal url As String) As Dictionary
    With New WinHttpRequest
        .Open "GET", "URL" ''[Should this be a random URL, my list of URLs?]
        .Send
        Set GetJson = JsonConverter.ParseJson(.ResponseText)
    End With
End Function

Sub FillTaxiInfo(data As Dictionary, sheet As Worksheet)
    Dim i As Integer, taxi As Dictionary
    For i = 0 To UBound(data("prices")) - 1
        Set taxi = data("prices")(i)
        If taxi.Exists("ViewDisplayName") Then
          sheet.Cells(i, 1) = taxi("ViewDisplayName")
          sheet.Cells(i, 2) = taxi("fare")("fareType")
        End If 
    Next i
End Sub

Sub FillMultipleCityInfo(myUrls As Variant, book As Workbook)
    Dim i As Integer, data As Dictionary, sheet As Worksheet

    For i = 0 To UBound(myUrls) - 1
        Set data = GetJson(myUrls(i))
        Set sheet = book.Sheets(i + 1)
        FillTaxiInfo data, sheet
    Next i
End Sub