title: Excel VBA extract values JSON URL
body: <p>In Excel</p>
  
  <p>I try to extract this value  "45.33887499999999"</p>
  
  <p>by this <strong>Google URL "<a href="https://maps.googleapis.com/maps/api/geocode/json?address=bojon" rel="nofollow">https://maps.googleapis.com/maps/api/geocode/json?address=bojon</a>"</strong> (in the example <strong>Google URL +</strong>"=bojon" or <strong>+</strong>"=VENICE%20BEACH%20CA")</p>
  
  <p>with this VBA code:</p>
  
  <pre><code>Public Function LATITUDE(coord As String)
      Dim firstVal As String
      firstVal = "https://maps.googleapis.com/maps/api/geocode/json?address="
      Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
      URL = firstVal &amp; Replace(coord, " ", "+")
      objHTTP.Open "GET", URL, False
      objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
      objHTTP.send ("")
      If InStr(objHTTP.responseText, """location"" : {") = 0 Then GoTo ErrorHandl
      Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """lat"".*?([0-9]+)": regex.Global = False
      Set matches = regex.Execute(objHTTP.responseText)
      tmpVal = Replace(matches(Index).SubMatches(0), ".", Application.International(xlListSeparator))
      LATITUDE = CDbl(tmpVal)
      Exit Function
  ErrorHandl:
      LATITUDE = -1
  End Function
  </code></pre>
  
  <p>but this code extracts only "45" and not "45.33887499999999"</p>
  
  <p>I tried to change regex.Pattern = """lat"".*?([0-9]+)"</p>
  
  <p>but I haven't found the solution</p>
  
  <hr>
  
  <p>Finally I want to extract 3 values with 3 different formulas (created by VBA code) from this URL</p>
  
  <p><strong>Google URL + "=bojon"</strong></p>
  
  <p>in these rows</p>
  
  <pre><code>     "formatted_address" : "30010 Bojon VE, Italia",
       "geometry" : {
          "location" : {
             "lat" : 45.33887499999999,
             "lng" : 12.06598
  </code></pre>
  
  <p>in A1 cell: "bojon"</p>
  
  <p>=GOOGDRESS(A1) result = "30010 Bojon VE, Italia"</p>
  
  <p>=LATITUDE(A1) result = "45.33887499999999"</p>
  
  <p>=LONGITUDE(A1) result = "12.06598"</p>
  
  <hr>
  
  <p>another example:</p>
  
  <p><strong>Google URL + "=VENICE%20BEACH%20CA"</strong></p>
  
  <pre><code>     "formatted_address" : "Venice Beach, California, Stati Uniti",
       "geometry" : {
          "bounds" : {
             "northeast" : {
                "lat" : 33.996311,
                "lng" : -118.4561299
             },
             "southwest" : {
                "lat" : 33.9636437,
                "lng" : -118.4835886
             }
          },
          "location" : {
             "lat" : 33.9936153,
             "lng" : -118.4799099
  </code></pre>
  
  <p>=GOOGDRESS(A1) result = "Venice Beach, California, Stati Uniti"</p>
  
  <p>=LATITUDE(A1) result = "33.9936153"</p>
  
  <p>=LONGITUDE(A1) result = "-118.4799099"</p>
  
  <hr>
  
  <p>can anyone help me?</p>