VBA formula for distance always returns zero??

[RogeratCCCC]

Hello:

I’m trying to create a VBA function that will return the distance in statute miles between two points expressed in decimal latitude and longitude. So far, I have:

Function Distxx(Lat1, Lon1, Lat2, Lon2)
Distxx = RadiusEarth * ((2 * Application.WorksheetFunction.Asin(Sqr((Sin((Application.WorksheetFunction.Radians(Lat1) - Application.WorksheetFunction.Radians(Lat2)) / 2) ^ 2) + Cos(Application.WorksheetFunction.Radians(Lat1)) * Cos(Application.WorksheetFunction.Radians(Lat2)) * (Sin((Application.WorksheetFunction.Radians(Lon1) - Application.WorksheetFunction.Radians(Lon2)) / 2) ^ 2)))))
End Function

but the formula returns zero for all inputs that I have tried. Can anyone tell me what’s wrong with it?

(And if there is a simpler way to do this, I would love to know what it is.)

Thanks !!

[romperstomper]

How/where is RadiusEarth defined?

[RogeratCCCC]

RadiusEarth in Statute Miles is a defined variable of: 4555.635783 miles

[Kyle123]

romper was asking where it is defined in your code, if it is not in the procedure, it will have a value of 0, the function will therefore always return 0

Put Option Explicit right at the top of the module before all the other code. If it errors, the variable has not been defined

[shg]

If you use a function that returns central angle intead, you can mutiple by the earth's radius in whatever units you like to get distance:

Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008

    ' Returns central angle between two point in RADIANS using Vincenty formula

    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#

    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double

    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lat2 = D2R * lat2
    dLon = D2R * (lon2 - lon1) ' delta lon

    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function

E.g., =3959*CentralAngle(lat1, lon1, lat2, lon2) returns statute miles

[RogeratCCCC]

Just tried the CentralAngle formula and it worked pretty well. So thanks !! I will use that.

[RogeratCCCC]

Thanks for your responses. As is obvious, I suppose, I'm not very familiar with VBA; I'm just trying to get something that works.
I"m not sure what Option Explicit does, so I just defined RadiusEarth within the Function. I would think that should work, but the result is still zero.
I haven't tried the Central Angle formula but will work on it this afternoon.
Here's the function with RadiusEarth defined (I think):
Any other comments appreciated....

Function Distxx(Lat1, Lon1, Lat2, Lon2)
RadiusEarth = 4555.635783
Distxx = RadiusEarth * ((2 * Application.WorksheetFunction.Asin(Sqr((Sin((Application.WorksheetFunction.Radians(Lat1) - Application.WorksheetFunction.Radians(Lat2)) / 2) ^ 2) + Cos(Application.WorksheetFunction.Radians(Lat1)) * Cos(Application.WorksheetFunction.Radians(Lat2)) * (Sin((Application.WorksheetFunction.Radians(Lon1) - Application.WorksheetFunction.Radians(Lon2)) / 2) ^ 2)))))
End Function

[Kyle123]

Like this:

Function Distxx(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double) As Double
Const RadiusEarth As Double = 4555.635783
With Application.WorksheetFunction
    Distxx = RadiusEarth * ((2 * .Asin(Sqr((Sin((.Radians(Lat1) - .Radians(Lat2)) / 2) ^ 2) + Cos(.Radians(Lat1)) * Cos(.Radians(Lat2)) * (Sin((.Radians(Lon1) - .Radians(Lon2)) / 2) ^ 2)))))
End With
End Function

[shg]

I don't know what planet has a radius of 4555 miles -- it's not Earth.