Excel Calculating Distance between two points.

[Matty5894]

Hi All,

Trying to create an easy to use function in VBA from the complicated formula below.

but i cant seem to get it working? Any help be much appreciated!

=IFERROR(6371*ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))),0)

Function CROWFLIES(dlat1 As Variant, dlat2 As Variant, dlon1 As Variant, dlon2 As Variant)
       
    Pi = 3.1415
    
    Rads = Pi / 180
    
    Acos1 = Application.WorksheetFunction.Acos(Argument)
    
    Errorcheck = Application.WorksheetFunction.IfError(Arg1, Arg2)

    CROWFLIES = Errorcheck(6371 * (Acos1(Cos(Rads(90 - dlat1))) * (Cos(Rads(90 - dlat2)) + Sin(Rads(90 - dlat1))) * (Sin(Rads(90 - dlat2))) * (Cos(Rads(dlon1 - dlon2)))), 0)
    
End Function

[estratosphere]

Just to clarify... are you trying to work out distance between two Lat/Long points? If so, why not save some time and use something like the Google API - it is free and simple to implement to calculate distance.

[Matty5894]

Yeah thats correct. Using Longitude and Latitude.

The issue is the sheer volume of points i would need to analyse a formula actually works out easier for the type of analysis i require