Btukfyl

I have a macro that calculates distances between two points. I am unable to get it to work, and would like some help debugging it.

I have created a Google API key, and have incorporated that in as well, but for some reason the macro doesn't work

Public Function



GetDT(origin_city As String, _

origin_state As String, origin_country As String, _

destination_city As String, _

destination_state As String, destination_country As String _

)



Dim surl                As String

Dim oXH                 As Object

Dim bodytxt             As String



Dim distanc_e           As String





surl = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" 

& _

Replace(origin_city, " ", "+") & "+" & Replace(origin_state, " ", "+") & 

"+" & Replace(origin_country, " ", "+") & _

"&destinations=" & _

Replace(destination_city, " ", "+") & "+" & Replace(destination_state, " 

", "+") & "+" & Replace(destination_country, " ", "+") & _

"&mode=driving&units=metric&key=MY_KEY"





Set oXH = CreateObject("msxml2.xmlhttp")



With oXH

    .Open "get", surl, False

    .send

    bodytxt = .responseText

End With



bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)

tim_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)





bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)

distanc_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)



GetDT = distanc_e



Set oXH = Nothing



End Function

There is no way to confidently answer this question with the information provided. Writing a separate function to create the url will make your code more testable. Using Option Explicit to force all variables to be declared will detect any typos.

If MY_KEY is a variable then the url should look like this "..metric&key=" & MY_KEY.

surl = GetDTURL(origin_city, origin_state, origin_country, destination_city, destination_state, destination_country)

Function GetDTURL(origin_city As String, origin_state As String, origin_country As String, destination_city As String, destination_state As String, destination_country As String)

    Dim surl As String

    Const BaseURl As String = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=@origin_city+@origin_state+@origin_country&destinations=@destination_city+@destination_state+@destination_country&mode=driving&units=metric&key=MY_KEY"

    surl = BaseURl

    surl = Replace(surl, "@origin_city", origin_city)

    surl = Replace(surl, "@origin_state", origin_state)

    surl = Replace(surl, "@origin_country", origin_country)

    surl = Replace(surl, "@destination_city", destination_city)

    surl = Replace(surl, "@destination_state", destination_state)

    surl = Replace(surl, "@destination_country", destination_country)

    surl = Replace(surl, " ", "+")

    GetDTURL = surl

End Function