I am writing VBA 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
The spreadsheet looks like this:
The process is working, but it is excruciatingly slow. The employee population is approximately 2300, and it takes almost an hour to execute.
I am not a coder, but I can functionally modify found code to my purposes. This is an amalgamation of multiple different processes found through Google searches. The code pieces in use are:
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
To be clear, the process works well, just extremely slowly. Any thoughts on speeding this up?
async/await
in .net - populating the sheet as the results come in (or all at once, after everything is done). \$\endgroup\$ – Mathieu Guindon♦ May 28 at 21:11MSXML2.ServerXMLHTTP
supports asynchronous requests - does it have to be UDFs, or can the code be refactored into a macro that populates the worksheet cells? Leveraging asynchronous requests could dramatically cut the time it takes to update this data. \$\endgroup\$ – Mathieu Guindon♦ May 29 at 15:35