2

I am trying to create a method via VBA when I use this method it says #NAME? and sometime #value.

Public Function DISTANCE(start As String, dest As String, key As String)
    
    Dim firstVal As String, secondVal As String, lastVal As String
    
    firstVal = "http://dev.virtualearth.net/REST/V1/Routes/Driving?wp.0="
    secondVal = "&wp.1=destinations="
    lastVal = "&optimize=time&routePathOutput=Points&distanceUnit=km&output=xml&key=" & key
    
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    URL = firstVal & start & secondVal & dest & lastVal
        
    objHTTP.Open "GET", URL, False
    objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.Send ("")
    DISTANCE = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.609, 0)
    
End Function
Community
  • 1
  • 1
Zahreen Khan
  • 23
  • 7

1 Answers1

1

Three things.

  1. You need to pass to and from locations as lower case along with , 2 letter abbr for state

  2. The above need to be urlencoded

  3. The response has a default namespace you need to add in. For the latter, I resort to using MSXML2.DOMDocument so as to be able to add the namespace.

Public Sub test()

    Debug.Print GetDistance("new york,ny", "miami,fl", "key")

End Sub

Public Function GetDistance(ByVal start As String, ByVal dest As String, ByVal key As String) As Long
    
    Dim firstVal As String, secondVal As String, lastVal As String, objHTTP As Object, url As String
    
    firstVal = "http://dev.virtualearth.net/REST/V1/Routes/Driving?wp.0="
    secondVal = "&wp.1=destinations="
    lastVal = "&optmz=time&routePathOutput=Points&distanceUnit=km&output=xml&key=" & key
    
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    url = firstVal & Application.EncodeURL(LCase$(start)) & secondVal & Application.EncodeURL(LCase$(dest)) & lastVal
    
    objHTTP.Open "GET", url, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send

    Dim xmlDoc As MSXML2.DOMDocument60 'reference to Microsoft XML via VBE>Tools>References
    
    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.LoadXML objHTTP.responseText
    xmlDoc.SetProperty "SelectionNamespaces", "xmlns:r='http://schemas.microsoft.com/search/local/ws/rest/v1'"

    GetDistance = Round(Round(xmlDoc.SelectSingleNode("//r:TravelDistance").Text, 3) * 1.609, 0)
    
End Function
QHarr
  • 83,427
  • 12
  • 54
  • 101