MS Excel VBA - As-the-crow-flies distance function

The MS Excel formula shown below calculates the as-the-crow-flies distance (in km), the shortest distance between two geographical locations specified by latitude and longitude. The user defined function globeDistance is much easier to read and link, and it can also be used in larger VBA procedures.

     Download demo file  (17kB - downloaded 537 times)


MS Excel formula

Given the following input fields (in degrees): From latitude (cell A2), From longitude (cell B2), To latitude (cell C2), To longitude (cell D2), the formula is


Dependent on your MS Excel setting you may have to replace the semicolon by a comma.

MS Excel VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the MS Excel VBA code below into the module.