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

The spreadsheet looks like this:

enter image description here

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 a couple different process found through Google searching. 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?

Thanks, Lee