Skip to content
Thaddeus Vincenty's Direct and Inverse formulae for geodesic calculations in Excel (distance, azimuth, latitude, longitude).
Branch: master
Clone or download
Latest commit a470514 Apr 20, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.gitignore NormalizeTest.xlsm ignored Jan 29, 2019
ExcelScreenshot.png improvements Apr 8, 2019
LICENSE Initial commit Jun 16, 2018
README.md Update README.md Apr 20, 2019
Vincenty.bas improvements Apr 8, 2019
Vincenty.xla improvements Apr 8, 2019
Vincenty.xlam improvements Apr 8, 2019
Vincenty.xls improvements Apr 8, 2019
Vincenty.xlsm improvements Apr 8, 2019
VincentyTest-RevAzimuth.xlsm improvements Apr 8, 2019
VincentyTest.xls improvements Apr 8, 2019
VincentyTest.xlsm improvements Apr 8, 2019

README.md

Vincenty's Direct and Inverse Solution of Geodesics on the Ellipsoid - Excel VBA implementation

to calculate new coordinate based on azimuth and distance (direct)
or distance and azimuth based on two coordinates (inverse)

Algorithms by Thaddeus Vincenty (1975)
Based on the implementation in Java Script by Chris Veness
https://www.movable-type.co.uk/scripts/latlong-vincenty.html
https://github.com/chrisveness/geodesy

To make the long story short, I was looking for a way to calculate coordinates, distance and azimuth in Excel. I checked out several available solutions but they were either incomplete, did not work or results were inaccurate. That is how I ended up developing my own, complete Vincenty's Direct and Inverse formulae implementation.

Excel screenshot

Excel files

  • There is no IntelliSense available for VBA UDFs. However, functions and their parameters are listed in Excel function wizard under the Geodesic or User Defined category.
  • Excel Add-in must be placed in a directory registered as "Trusted Location". See Add, remove, or change a trusted location for more details. Demo workbook does not require Add-in.

Implementation

Solution contains 6 functions implementing Vincenty's Direct and Vincenty's Inverse formulae as well as 2 functions for Decimal ↔ Degrees/Minutes/Seconds format conversion, and uses WGS84 model.

  • VincentyDirLat(lat as Double, lon as Double, azimuth as Double, distance as Double) as Variant Calculates geodesic latitude (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids.
  • VincentyDirLon(lat as Double, lon as Double, azimuth as Double, distance as Double) as Variant Calculates geodesic longitude (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids.
  • VincentyDirRevAzimuth(lat as Double, lon as Double, azimuth as Double, distance as Double, [returnAzimuth as Boolean = False]) as Variant Calculates geodesic reverse azimuth (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids. Note: by default aziumuth from point 1 to point 2 at point 2 is returned. To obtain azimuth from point 2 to point 1 pass returnAzimuth = true.
  • VincentyInvDistance(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double) as Variant Calculates geodesic distance (in m) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids.
  • VincentyInvFwdAzimuth(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double) as Variant Calculates geodesic azimuth (in degrees) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids.
  • VincentyInvRevAzimuth(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double, [returnAzimuth as Boolean = False]) as Variant Calculates geodesic reverse azimuth (in degrees) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids. Note: by default aziumuth from point 1 to point 2 at point 2 is returned. To obtain azimuth from point 2 to point 1 pass returnAzimuth = true.
  • ConvertDegrees(decimalDeg as Double, optional isLongitude as Variant) as String Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string format. If isLongitude value is privided output will be formatted as either longitude (true) or latitude (false).
  • ConvertDecimal(degreeDeg as String) as Variant Converts latitude, longitude or azimuth string in degrees/minutes/seconds format to decimal value. This function has been designed to parse typical formats.
  • NormalizeLat(lat as Double) as Double Normalizes latitude to -90..+90 range.
  • NormalizeLon(lon as Double) as Double Normalizes longitude to -180..+180 range.
  • NormalizeAzimuth(azimuth as Double, [positiveOnly as Boolean = False]) as Double Normalizes azimuth to 0..360 range. Note: by default input and return values have the same sign. To obtain only positive values pass positiveOnly = true.

Source code

Excel workbooks contain unprotected source code. In addition, for better change tracking, source code has been placed separately in Vincenty.bas file. This file is all what is required to add implemented functions to any other Excel workbook.

Validation

Calculation results have been validated using 1200 test cases generated for 6 range clusters and distance between 10 m and 30,000 km against Geoscience Australia website:

and GeodSolve Library by Charles Karney:

Validation results - maximum deviation

  Geoscience Australia GeodSolve Library
VincentyDirLat() 0.0000005% 0.0000000%
VincentyDirLon() 0.0000002% 0.0000001%
VincentyDirRevAzimuth() 0.0000833% 0.0000000%
VincentyInvDistance() 0.0024183% 0.0001269%
VincentyInvFwdAzimuth() 0.0008098% 0.0003928%
VincentyInvRevAzimuth() 0.0005245% 0.0003928%

For complete test results refer to VincentyTest.xlsm file.

References

Feedback

Do not hesitate to rate this repo and/or leave comments/suggestions if you find my work useful.

You can’t perform that action at this time.