Google drive distance function between latitude, longitude.

[okamymiller]

I need a google function code, vba, that will get the driving distance in miles , between lat and long locations. I have a function for Bing Maps, but it doesn't provide the accuracy that google mapping does. I have my own Google " api key". I've tried several vba's that I found online they just don't seem to be working or include drive times and other information I don't need.

FYI I have a spreadsheet that has multiple lat and longs that I have to find the mileage for multiple destinations. It is very time consuming on google web map putting them in.

Any help appreciated, its my day job.

[Richard Buttrey]

How far apart are the longitude points and how accurate do the results have to be.
For smaller distances it may be easier to assume a Euclidean plane and use Pythogoras theorem where differences in Latitude are ~ 69 ,miles per degree and at your latitude location a degree of longitude ~56 miles

[okamymiller]

How far apart are the longitude points and how accurate do the results have to be.
For smaller distances it may be easier to assume a Euclidean plane and use Pythogoras theorem where differences in Latitude are ~ 69 ,miles per degree and at your latitude location a degree of longitude ~56 miles

They are typically within 5 to 10 miles of each other in the same county. When I attempt with bing it takes the long way around or doesn't have all the roads listed. I'm good with typical drive times from point a to point b. My points are always long and lats because the areas I use are not physical addresses.

[Richard Buttrey]

OK,

But note these are not ROAD distances but straight line
The simple Pythagoras version - numbers are Degrees
With Lat 1 in A1, Lat 2 in A2, Long 1 in B1 & Long B in B2
Distance in miles is given by

Please Login or Register  to view this content.

For a more accurate calculation over longer distances where there are large changes of latitude then the Haversine formula is better
In C1 enter =RADIANS(A1), C2 =RADIANS(A2), D1 =RADIANS(B1), D2 =RADIANS(B2)
Apply the following range names C1: LATA, C2: LATB, D1: LONA, D2: LONB

Then the distance formula is

Please Login or Register  to view this content.

[okamymiller]

Okay thank you , I do have to have driving distances since the transport mode is vehicles. I need a formula that uses the google api. I have found google drive distances to be the most accurate for my work.

[ChemistB]

Does this link help?
https://analystcave.com/excel-calcul...een-addresses/
includes directions for coordinates

[okamymiller]

I have tried this one before, it keeps coming back with the -1 error in the calculation box.

[Haluk]

@okamymiller; Did you try it with your own API key?

[okamymiller]

Yes I put in my own API I created at google. I've seen this same error from others trying to use this code as well. I don't know enough about VBA to figure out whats wrong with it.