# Speeding Up Excel Distance Calculation Using Bing API Calls

I am writing VB code in Excel to calculate the distance between an employee’s home address and work address using Bing Maps API calls. The process follows this general flow:

1) Convert the employee’s address to Lat-Long values using the GetLatLong function

2) Convert the employee’s work address to Lat-Long values using the GetLatLong function

3) Calculate the distance between these two points using the GetDistance function

4) Calculate the drive time between these two points using the GetTime function

``Public Function GetDistance(start As String, dest As String)     Dim firstVal As String, secondVal As String, lastVal As String     firstVal = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="     secondVal = "&destinations="     lastVal = "&travelMode=driving&o=xml&key=<My Key>&distanceUnit=mi"     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 ("")     GetDistance = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDistance"), 0) & " miles" End Function  Public Function GetTime(start As String, dest As String)     Dim firstVal As String, secondVal As String, lastVal As String     firstVal = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="     secondVal = "&destinations="     lastVal = "&travelMode=driving&o=xml&key=<My Key>&distanceUnit=mi"     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 ("")     GetTime = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDuration"), 0) & " minutes" End Function  Public Function GetLatLong(address As String, city As String, state As String, zip As String)     Dim firstVal As String, secondVal As String, thirdVal As String, fourthVal As String, lastVal As String     firstVal = "https://dev.virtualearth.net/REST/v1/Locations?countryRegion=United States of America&adminDistrict="     secondVal = "&locality="     thirdVal = "&postalCode="     fourthVal = "&addressLine="     lastVal = "&maxResults=1&o=xml&key=<My Key>"     Url = firstVal & state & secondVal & city & thirdVal & zip & fourthVal & address & lastVal     Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")     objHTTP.Open "GET", Url, False     objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"     objHTTP.send ("")     GetLatLong = WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Latitude") & "," & WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Longitude") End Function ``