CODEWITHSUNDEEP
vbaexcel
Kyle
Kyle

Reputation: 77

How do I organize and structure multi-sub/function VBA code such that it actually runs?

How do I setup a run/macro to execute the below code. I've tried many things, including creating a new module that only calls each of the subs individually -- no luck. The output for this project should be an excel workbook that contains worksheets (4 rows x 8 columns) with parsed JSON text in each cell. The worksheets correspond to unique URLs. For example, Sheet1 = URL1 data, Sheet2 = URL2 data, etc.

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

Upvotes: 3

Views: 1945

Answers (1)

Comintern
Comintern

Reputation: 22195

Move the global variable to the top, then make an entry point sub that initializes it and executes 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

Upvotes: 2

Related Questions