Hello everyone
I have the following UDF that calculates the distance between two addresses
But it is very slow and most of the time it doesn't return correct results ..
Please Login or Register to view this content.
There is a sample of the origonal file .. the original file has more then 15000 addresses ..
Is there a faster way ?
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
I haven't tested this, but my hunch is that you are not going to get useable results using zip codes. They are simply not accurate enough.
I would suggest a 2 stage proces as follows. If this does not work for you, search for the words Haversine and Vincenty in connection with Excel on Google for more options using some heavy duty trigonometry.
1) get the latitude and longitude co-ordinates of each address. Some suggested code can be found at Chandoo's site: http://forum.chandoo.org/threads/fin...-and-vba.7253/
2) then calculate the distances between them using the formula shown here - http://bluemm.blogspot.co.uk/2007/01...-distance.html - which I reproduce here in case the link dies (this calculates in miles).
Hope this works.Please Login or Register to view this content.
Thanks a lot for these useful links
Best Regards
Thanks a lot for the link
I inserted the udf function and refer to the refernece Microsoft XML v6.0 but got the following error
User defined type not defined
And please put an example if possiblePlease Login or Register to view this content.
Try changing it to Microsoft XML v3
Thanks a lot for help
What about YOUR_MS_KEY ?? I naviagted to the link you provided but didn't know how to get the key
And can you post an example for the formulas =getdistance(need example) ..?
Mr. Kyle
sorry for disturbing you .. can you provide me the code again as I noticed in replies in the original thread that some members do some changes to the code...?
Thanks advanced for any help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks