Running Formula Multiple Times Returns Value Error

angie2011

New Member
Joined
Jul 19, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top