title: VBA MACRO: Trying to edit a VBA Macro that returns json data
body: <p>Please see the code below which works to query an api call. I show extracting only specific items from json. There is a lot more info returned e.g. all current employments which can be more than 1. How can the code be modified to query by state?</p>
  
  <pre><code>Option Explicit
  'Individual
  Public r As Long
  
  Public Sub GetListings2()
      '&lt;  VBE &gt; Tools &gt; References &gt; Microsoft Scripting Runtime
      Dim json As Object, apiUrl As String, re As Object, s As String, latLon()
      r = 0
      Set re = CreateObject("VBScript.RegExp")
      apiUrl = "https://api.brokercheck.finra.org/individual?hl=true&amp;includePrevious=false&amp;json.wrf=angular.callbacks._d&amp;lat={LAT}&amp;lon={LON}&amp;nrows=100&amp;r=25&amp;sort=score+desc&amp;{START}&amp;wt=json"
      Dim xhr As Object, totalResults As Long, numPages As Long
  
      Set xhr = CreateObject("MSXML2.XMLHTTP")
  
      latLon = GetLatLon("30047", xhr, re)
      apiUrl = Replace$(Replace$(apiUrl, "{LAT}", latLon(0)), "{LON}", latLon(1))
      s = GetApiResults(xhr, Replace$(apiUrl, "{START}", "start=0"), re)
  
      If s = "No match" Then Exit Sub
  
      Set json = JsonConverter.ParseJson(s)("hits")
  
      totalResults = json("total")
  
      numPages = Application.RoundUp(totalResults / 100, 0)
  
      Dim results(), ws As Worksheet, headers(), i As Long
  
      'example info retrieved. There is a lot more info in json
      headers = Array("CRD Number Indiv", "Name", "FINRA registered", "Disclosures", "In industry since")
      ReDim results(1 To totalResults, 1 To UBound(headers) + 1)
  
      Set ws = ThisWorkbook.Worksheets("Sheet1")
  
      results = GetIndividualListings(results, json("hits"))
      If numPages &gt; 1 Then
          For i = 2 To numPages
              DoEvents
              s = GetApiResults(xhr, Replace$(apiUrl, "{START}", "start=" &amp; (i - 1) * 100), re)
              If s = "No match" Or InStr(s, "Exceeded limit") &gt; 0 Then Exit For
              Set json = JsonConverter.ParseJson(s)("hits")
              results = GetIndividualListings(results, json("hits"))
          Next
      End If
      With ws
          .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
          .Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
      End With
  End Sub
  
  Public Function GetLatLon(ByVal zip As String, ByVal xhr As Object, ByVal re As Object) As Variant
      Dim json As Object, lat As String, lon As String
      With xhr
          .Open "GET", Replace$("https://api.brokercheck.finra.org/locations?query={ZIP}&amp;results=1", "{ZIP}", zip), False 'changed results = 10 to results = 1
          .send
          Set json = JsonConverter.ParseJson(.responseText)("hits")("hits")(1)("_source")
          lat = json("latitude")
          lon = json("longitude")
          GetLatLon = Array(lat, lon)
      End With
  End Function
  
  Public Function GetApiResults(ByVal xhr As Object, ByVal apiUrl As String, ByVal re As Object) As String
      With xhr
          .Open "GET", apiUrl, False
          .send
          GetApiResults = GetJsonString(re, .responseText)
      End With
  End Function
  
  Public Function GetIndividualListings(ByVal results As Variant, ByVal json As Object) As Variant
      Dim row As Object
        'can have numerous current employments. Alter here and below if want more info from json about the individual
  
      For Each row In json
          r = r + 1
          results(r, 1) = row("_source")("ind_source_id")
          results(r, 2) = Replace$(Join$(Array(row("_source")("ind_firstname"), row("_source")("ind_middlename"), row("_source")("ind_lastname")), ", "), ", , ", ", ")
          results(r, 3) = row("_source")("ind_approved_finra_registration_count")
          results(r, 4) = row("_source")("ind_bc_disclosure_fl")
          results(r, 5) = row("_source")("ind_industry_cal_date")
      Next
      GetIndividualListings = results
  End Function
  
  Public Function GetJsonString(ByVal re As Object, ByVal responseText As String) As String
      With re
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .Pattern = "\((.*)\);" 'regex pattern to get json string
          If .Test(responseText) Then
              GetJsonString = .Execute(responseText)(0).SubMatches(0)
          Else
              GetJsonString = "No match"
          End If
      End With
  End Function
  </code></pre>
  
  <p>The results work but how can the query change to query against state instead of zipcode?</p>