Whole circle bearing calculation with vba

[Westbury]

Hi,

I'm trying to use this expression in a macro to obtain the whole circle bearing between 2 points of known latitude and longitude, the LatA Lonb etc (all in radians)


[FONT=&quot]360-(MOD(ATAN2((COS(LatA)*SIN(LatB))-(SIN(LatA)*COS(LatB)*COS(LonB-LonA)), SIN(LonB-LonA)*COS(LatB)),2*PI())*180/PI())


I think ATAN2 has to be called up as a worksheetfunction, but how do I use MOD in a macro?

Thanks
Geoff [/FONT]

 

[JoeMo]

Assuming LatA, LatB, LonA, LonB are vba variables you could use the Evaluate function like this:

Sub EvalIt()
Dim LatA, LatB, LonA, LonB, WholeCirBearing
LatA = 30.267153: LonA = -97.743057: LatB = 29.760427: LonB = -95.369804
WholeCirBearing = Evaluate("360-(MOD(ATAN2((COS(" & LatA & ")*SIN(" & LatB & _
    "))-(SIN(" & LatA & ")*COS(" & LatB & ")*COS(" & LonB - LonA & ")), SIN(" & _
    LonB - LonA & ")*COS(" & LatB & ")),2*PI())*180/PI())")
MsgBox WholeCirBearing
End Sub

As a caution, I know nothing about whole circle bearings so I may be missing the boat here altogether, but the values I used for the variables are the Lat/Lon for A is Austin,Tx and B is Houston, Tx. The answer I get is 170.534524833916 which means absolutely nothing to me.

 

[Westbury]

Joe, Thanks for your reply.

The whole circle bearing from Austin to Houston is approx 103 degrees. I've used an online calculator https://www.movable-type.co.uk/scripts/latlong.html to obtain this figure. [the WCB is the angle measured with zero at north to the line between the 2 points under investigation.

Your code produces a result but it's not correct. I'm sure that the formula agrees with mine which is widely available on the net.

Gone away to think....

Geoff

 

[JoeMo]

Joe, Thanks for your reply.

The whole circle bearing from Austin to Houston is approx 103 degrees. I've used an online calculator https://www.movable-type.co.uk/scripts/latlong.html to obtain this figure. [the WCB is the angle measured with zero at north to the line between the 2 points under investigation.

Your code produces a result but it's not correct. I'm sure that the formula agrees with mine which is widely available on the net.

Gone away to think....

Geoff

Can't help you with the answer produced,nor the validity of the equation you posted for whole circle bearing, but the method of calculation using VBA is what I wanted to convey.

 

[Westbury]

Understood, thanks.

 

[JoeMo]

Understood, thanks.

You are welcome - thanks for the reply.