Konudan anlaşılıyor ama test etmek lazım.

• Excelde hiç data tutmadan tamamen Google maps verilerine göre mesafe hesaplayabiliyor.
• İsimden  veya koordinat kullanarak hesaplama yapabiliyor. İsimde en yakın değeri kullanıyor.
• Zaman saat cinsinden, uzaklık km cinsinden veriliyor.
• feribotlar devre dışı bırakıldı. feribotları devreye almak için avoid=ferries ifadesini çıkarmak gerekiyor

KOD

Public Function MesafeHesapla(baslangic As String, bitis 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=tr&sensor=false&avoid=ferries”
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = firstVal & Replace(baslangic, ” “, “+”) & secondVal & Replace(bitis, ” “, “+”) & 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(Index).SubMatches(0), “.”, Application.International(xlListSeparator))
MesafeHesapla = CDbl(tmpVal) / 1000
Exit Function
ErrorHandl:
MesafeHesapla = -1
End Function

Public Function SureHesapla(baslangic As String, bitis 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&sensor=false&avoid=ferries”
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = firstVal & Replace(baslangic, ” “, “+”) & secondVal & Replace(bitis, ” “, “+”) & 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, “””duration”” : {“) = 0 Then GoTo ErrorHandl
Set regex = CreateObject(“VBScript.RegExp”): regex.Pattern = “duration(?:.|\n)*?””value””.*?([0-9]+)”: regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(Index).SubMatches(0), “.”, Application.International(xlListSeparator))
SureHesapla = CDbl(tmpVal) / 3600
Exit Function
ErrorHandl:
SureHesapla = -1
End Function