asked on

convert simple function to vba

Hi! I have this function... i need to use it in a ms access module...

can you help me? thanks

---------------------------------------------------------------------------------------
// lat1, lon1 double Latitude / Longitude of point 1(decimal degrees)
// lat2, lon2 double Latitude / Longitude of point 2 (decimal degrees)
// units char S-Statute Miles; N-Nautical Miles; K-Kilometers

#define pi 3.14159265358979323846

double DistLatLong(double lat1, double lon1, double lat2, double lon2, char units) {

double dlon, dlat;
dlon = lon2 - lon1;
dlat = lat2 - lat1;
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2;
c = 2 * atan2( sqrt(a), sqrt(1-a) );

// R (Earth Radius) = 3956.0 mi = 3437.7 nm = 6367.0 km

switch(units)
{
case 'S': // STATUTE MILES
R = 3956.0;
break;
case 'N': // NAUTICAL
R = 3437.7;
break;
case 'K': // KILOMETERS
R = 6367.0;
break;
}

return (R * c);
}

catalini

ASKER

i've found also this SQL version... is there any way to use it in a ms access query?

CREATE FUNCTION DistLatLong (@lat1 float, @lon1 float, @lat2 float, @lon2 float)
RETURNS float
AS
BEGIN
-- Parameters in RADIANS, result in statute miles
DECLARE @dlon float, @dlat float, @a float, @c float
SET @dlon = @lon2 - @lon1 SET @dlat = @lat2 - @lat1
SET @a = POWER(SIN(@dlat / 2.0), 2.0) +
COS(@lat1) * COS(@lat2) * POWER(SIN(@dlon / 2.0), 2.0)
SET @c = 2.0 * ATN2(SQRT(@a), SQRT(1.0 - @a))
RETURN 3956.0 * @c
END
GO
CREATE FUNCTION dms2rad (@deg int, @min int, @sec float)
RETURNS float
AS
BEGIN
RETURN RADIANS(CONVERT(float, @deg) + CONVERT(float, @min)/60.0 + @sec/3600.0)
END

rockiroads

try this

Public Function DistLatLong(ByVal lat1 As Double, _
ByVal lon1 As Double, _
ByVal lat2 As Double, _
ByVal lon2 As Double, _
ByVal units As String) As Double

Dim dlon, dlat As Double
Dim a As Double
Dim c As Double
Dim R As Double

dlon = lon2 - lon1
dlat = lat2 - lat1

a = (Sin(dlat / 2)) ^ 2 + Cos(lat1) * Cos(lat2) * (Sin(dlon / 2)) ^ 2
c = 2 * atan2(Sqr(a), Sqr(1 - a))

Sqr
'R (Earth Radius) = 3956.0 mi = 3437.7 nm = 6367.0 km

Select Case units
Case "S" 'STATUE MILES
R = 3956
Case "N" ' NAUTICAL
R = 3437.7
Case "K" 'KILOMETERS
R = 6367
End Select

DistLatLong = R * c

End Function

rockiroads

oops, atan2 line needs redoing, forgot that one

catalini

ASKER

thanks... what do you mean with redoing?

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

catalini

ASKER

thanks a lot... i will try it very soon... if i have problems i will tell you! :-))))

rockiroads

here is the site that I got atan2 from

http://mypage.bluewin.ch/w.stucki/Programmes.htm

So I dont know what value it returns to your C code.

The code has been converted, u can compare the two and easily spot the comparisons/differences

rockiroads

To prove whether that atan2 function worked, I checked in Excel

atan2 works in Excel so I did

=atan2(10,12)

this set the cell value to be 0.876058051

Now I called the atan2 function provided, and it returned 0.876058050598193

so pretty close, dont u think

catalini

ASKER

great!

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Markus Fischer

Hello rockiroads,

We found the same ATan2 function, but you forgot to reverse the arguments. Microsoft ATan2 is not the same as atan2 in the rest of the computing world, don't ask me why...

Try this in VB:

? Excel.WorksheetFunction.Atan2(1,10)

And this in the address bar of your browser:

javascript: alert(Math.atan2(1,10)+'\n'+Math.atan2(10,1))
javascript: alert(Math.atan2(10,12)) // to compare with 0.876058051

But you were fast... as usual

(°v°)

rockiroads

Catalini, Ive got a rogue statement

Sqr

its just above the comment for 'R (Earth Radius)

rockiroads

Hi Harfang,

yes, the atan2 functions are the same, and my test comparison was in excel. I didnt have any other tools

see my previous post about the test that I did

Anyway, Catalini will soon find out if it works or not and possibly where its failing so that it can be corrected

catalini

ASKER

thanks to both of you!!!!!