Distance over 1,000 miles returning just the first number.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)


Whenever I use this code, and the distance that is calculated is over 1,000 miles (or kilometers if I change the distance to kilometers), the result returned is just the first number. So if it’s 1,000 miles, it returns a 1, if it’s 2,000 it returns a 2, etc.

Below is the code I am using. Does anyone know why it’s doing this, and what I can do to correct the issue?


'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 = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=car&language=pl&units=imperial&sensor=false"
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 regex = CreateObject("VBScript.RegExp"): regex.Pattern = """text"".*?([0-9]+)": regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
Debug.Print matches(0).SubMatches(0)
tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
Debug.Print matches(0).SubMatches(0)
GetDistance = CDbl(tmpVal)
Exit Function
GetDistance = -1
End Function

Simply the best place to learn Excel VBA