-2

For years in Excel 2007 I used the Public Function GetDistance () VBA code in a module added in Visual Basic as given below (with my API key removed and replaced with "YOUR_KEY"). Now in Microsoft 365 excel and Excel 2007, the code returns the error handling value of -1, regardless of what addresses are given. Any idea why?

'Calculate Google Maps distance between two addresses

Public Function GetDistance(start As String, dest As String)

    Dim firstVal As String, secondVal As String, lastVal As String

    firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="

    secondVal = "&destinations="

    lastVal = "&mode=car&language=pl&sensor=false&key=YOUR_KEY"

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal

    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, """distance"" : {") = 0 Then GoTo ErrorHandl

    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False

    Set matches = regex.Execute(objHTTP.responseText)

    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))

    GetDistance = CDbl(tmpVal)

    Exit Function

ErrorHandl:

    GetDistance = -1

End Function

I tried running the code that worked in 2021, and now it returns an error handling value of -1. I tried the following with no improvement:

  • regenerated my API key, no change
  • tried both variants, no change:
  1. firstVal = "https://maps.googleapis.com...."
  2. firstVal = "http://maps.googleapis.com..."

I did try some other things, but I'm not sure they are worth mentioning here....

taller_ExcelHome
  • 2,232
  • 1
  • 2
  • 12
Brooke Loader
  • 1
  • 1
    The error handling value of -1 indicates that the function is not able to retrieve the distance from the Google Maps API. There could be several reasons for this. API Key Issues: ensure that it's correctly set up in the Google Cloud Console, and that the Distance Matrix API is enabled for that key. Billing Issues: Google Maps API requires billing information. Ensure that your billing details are up-to-date. – Joaquin Aug 27 '23 at 20:50
  • 1
    HTTP Request Issues: The method you're using to send HTTP requests (MSXML2.ServerXMLHTTP) might be outdated or blocked by Google. You might want to consider updating this method or using a different method to send HTTP requests. Error Messages: Instead of directly going to the error handler when InStr(objHTTP.responseText, """distance"" : {") = 0, you might want to print out objHTTP.responseText to see if Google is returning any error messages. This can give you more insight into what's going wrong. – Joaquin Aug 27 '23 at 20:51
  • related, possible duplicate of [GoogleMaps API Macro/VBA Not Returning DriveTime & DriveDistance Anymore](https://stackoverflow.com/questions/76970916/googlemaps-api-macro-vba-not-returning-drivetime-drivedistance-anymore) (doesn't have an answer yet) – geocodezip Aug 27 '23 at 23:47

0 Answers0