﻿ Excel - As-the-crow-flies distance (UDF)

# Excel As-the-crow-flies distance

The demo file offers three ways to calculate the as-the-crow-flies distance, the shortest distance between two geographical locations specified by latitude and longitude
1. Regular formula. It is a pretty long one, that might be difficult to link to its inputs and/or copy across.
2. VBA - User Defined Function (UDF) GlobeDistance. Much easier to read and link. But will execute slower. You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers. Default is in kilomters!
3. LAMBDA function L_GlobeDistance. LAMBDA functionality exists since Office365/Excel 2021. LAMBDA functions are user defined functions created directly within Excel's Name Manager, without VBA code. Works the same as the VBA UDF GlobeDistance. You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers (if omitted, then it returns the distance in kilometers, as its default!).

## Regular formula

Given the following input fields (in degrees): From latitude (cell A4), From longitude (cell B4), To latitude (cell C4), To longitude (cell D4), InMiles (cell E4) the formula is

## User Defined Function GlobeDistance: 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.

Note: in general, a user defined function will execute slower than a standard function, which can become a disadvantage in case of a large amount of formulas.

## LAMBDA Function L_GlobeDistance

Dependent on your Excel setting you need to use semicolon or comma in formulas. Best is to open the demo file and copy the formula from there, as it will contain your local settings. Else - if required - copy in function in text editor and replace comma by semicolon before copying into Excel.