I've been working on a project that requires the calculation of distance from a starting Latitude/Longitude to an ending Latitude/Longitude. After scouring the internet, I found half a dozen formulas and code scripts to do this. They are all slightly different, but give an appropriate mileage calculation.
.
The problem is that I want a generic function that works in both Excel and Access. And most of the scripts I found use mathematical functions that aren't available in Access VBA.
.
So I took some existing code and altered it to create this user defined function that you can plug into a module in either Excel or Access.
Nothing earth shattering I know, but it's nice to have this one piece of code in my library and have it work with both Excel and Access.
.
This code calculates miles based four coordinate points: Starting Latitude, Starting Longitude, Ending Longitude, and Ending Longitude. This means, you pass the function four arguments:
GetMiles(39.0826418, -76.8260106, 38.47444444, -76.7963888)
I won't attempt fully explain the math here, but this particular operation is using the Haversine formula to perform a great-circle distance calculation.
.
-
Function GetMiles(lat1Degrees As Double, lon1Degrees As Double, lat2Degrees As Double, lon2Degrees As Double)
-
-
Dim earthSphereRadiusKilometers As Double
-
Dim kilometerConversionToMilesFactor As Double
-
Dim lat1Radians As Double
-
Dim lon1Radians As Double
-
Dim lat2Radians As Double
-
Dim lon2Radians As Double
-
Dim AsinBase As Double
-
Dim DerivedAsin As Double
-
-
'Mean radius of the earth (replace with 3443.89849 to get nautical miles)
-
earthSphereRadiusKilometers = 6371
-
-
'Convert kilometers into miles (replace 0.621371 with 1 to keep in kilometers)
-
kilometerConversionToMilesFactor = 0.621371
-
-
'Convert each decimal degree to radians
-
lat1Radians = (lat1Degrees / 180) * 3.14159265359
-
lon1Radians = (lon1Degrees / 180) * 3.14159265359
-
lat2Radians = (lat2Degrees / 180) * 3.14159265359
-
lon2Radians = (lon2Degrees / 180) * 3.14159265359
-
-
AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^ 2))
-
DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))
-
-
'Get distance from [lat1,lon1] to [lat2,lon2]
-
GetMiles = Round(2 * DerivedAsin * (earthSphereRadiusKilometers * kilometerConversionToMilesFactor), 2)
-
-
End Function
.
Note that you will need to have your coordinates in decimal degrees (39.0826418) versus the time formatted degrees you sometimes see (for example, 39° 4' 57).
You can convert time formatted coordinates to decimal degrees by using
Degree + Minute /60 + Seconds /60/60
So for example 39° 4' 57 can be formatted to decimal degrees with this formula: 39+4/60+57/60/60
.
Ideally, your starting data will looks something like this:
.
To use this code in Access, copy the code into a standard module. Then you can reference it in queries or VBA.
.
To use this code in Excel, copy the code to a standard module. Then you can use it directly on your spreadsheet as a function.
..
Have a great week.
Direct distance? Hopefully this project is for an airline, and not a trucking firm.
You know you can type WorksheetFunction.Pi instead of typing Pi to 13 lucky digits...
Jeff: For our purposes, straight line distance gives us a directionally correct view of mileage performance.
deadeye: Believe it or not, Access VBA doesn't have the PI function built it. So to make the code generic, I had to use 3.14159265359.
Same with the ASin function. I had to use some derived math to get to the Asin.
I initally tried adding a reference to he Excel Object model to gain access to Excel's WorksheetFunction methods, but that slowed the code down greatly when calculating miles for lots of records.
So I just went with a generalized procedure without the use of derived math.
Just one small comment: those duplicate constant values make me uncomfortable; my eyes would itch less if the conversion to radians was performed by a function.
@datapig
Good point. I didn't realize that Access VBA didn't have a predefined function for Pi.
I saw your post just as I was writing something on the same problem, but solving it using the Excel 2013 WebService() function:
http://cwebbbi.wordpress.com/2013/02/26/bringing-location-information-into-excel-2013-with-the-webservice-function/
Fantastic!