Running Formula Multiple Times Returns Value Error

[angie2011]

I have a forumla that uses bing maps to get the distance between two sets of coordinates. My 'site' coordinates are in column B (800+) and my 'technician' coordinates are in row 3. I also have my bing key in A2.
I wrote the code so that I can enter formula =getdistance(Site coordinates, Technician coordinates, Key) and it will pull the coordinates based on those. When I put formula in row 4 to get distance from each site to the technician, I get the #VALUE! error in about 500 of my 800 rows. I know the formula is correct and works because if I click into each cell it will recalculate and show the correct number.
Also if I screw up and do not do a copy/paste values - sometimes the sheet will recalculate and I will lose some that had previously worked. I've searched and do not see another issue like this anywhere. My internet connection is great and I have even plugged directly into the modem instead of using wifi.

View attachment 51388

This is the code I wrote is setting in the Module of visual basic
:
Public Function GetDistance(start As String, dest As String, key 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=" & 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(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.515, 0)

End Function

 

[bstory84]

Have you tried capturing the error?
Possibly see if the HTTP response has an error code....
Something like the below text. Capture the HTTP status and text in a variable. Then output it somewhere like console or in a cell for you to see visually.

On Error Resume Next
Dim aErr As Variant
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 ("")
aErr = Array(ObjHTTP.Status, ObjHTTP.statusText)
GetDistance = Round(Round(WorksheetFunction.FilterXML(ObjHTTP.ResponseText, "//TravelDistance"), 3) * 1.515, 0)

 

[angie2011]

Have you tried capturing the error?
Possibly see if the HTTP response has an error code....
Something like the below text. Capture the HTTP status and text in a variable. Then output it somewhere like console or in a cell for you to see visually.

On Error Resume Next
Dim aErr As Variant
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 ("")
aErr = Array(ObjHTTP.Status, ObjHTTP.statusText)
GetDistance = Round(Round(WorksheetFunction.FilterXML(ObjHTTP.ResponseText, "//TravelDistance"), 3) * 1.515, 0)

Thank you so much for responding!
Even with this change it still does it. I'm wondering if my computer processor just sucks. I did look and it is draining the memory when I run them, so I am asking for a new laptop with a faster processor and more memory and such. I thought about putting a pause in the code, but that will take forever to run when I get 3-5 techs at once.

 

[bstory84]

I apologize for not being clearer. In order to troubleshoot . . I was suggesting to capture the error being returned to furhter investigate the matter. Since it appears at this point you are unsure as to what is causing the error. Once the actual error is known ,whether VBA related or Web (HTTP) related. Then solving will be easier.

 

[angie2011]

I apologize for not being clearer. In order to troubleshoot . . I was suggesting to capture the error being returned to furhter investigate the matter. Since it appears at this point you are unsure as to what is causing the error. Once the actual error is known ,whether VBA related or Web (HTTP) related. Then solving will be easier.

I actually got it to work yesterday. I was sent a new laptop with a faster processor and now it seems to work fine. Thank you for your assistance.