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:

Please Login or Register  to view this content.

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

[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

[RogeratCCCC]

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

[Kyle123]

Like this:

Please Login or Register  to view this content.

[shg]

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