I’m hoping someone can let me know what is wrong with the following VBA formula. I have purposefully made the formula identical in both cases as it better illustrates the problem.
If Sin((Lon2) - (Lon1)) = 0 Then
CalcTrueCourse2 = WorksheetFunction.Acos((Sin(Lat2) _
- Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _
* 180 / WorksheetFunction.Pi
Else
CalcTrueCourse2 = WorksheetFunction.Acos((Sin(Lat2) _
- Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _
* 180 / WorksheetFunction.Pi
End If
My problem is that the CalcTrueCourse2 formula works correctly when a number other than 0 is returned for Sin((Lon2)-(Lon1)) but gives a #Value error when returning 0. Surely the result should be identical?
If it helps, this is the entire function with the original formula:
Public Function CalcTrueCourse(varLat1, varLon1, varLat2, varLon2, varDist)
Dim Lat1 As Single
Dim Lat2 As Single
Dim Lon1 As Single
Dim Lon2 As Single
Lat1 = WorksheetFunction.Radians(ConvToDecDeg(varLat1)) * -1
Lat2 = WorksheetFunction.Radians(ConvToDecDeg(varLat2)) * -1
Lon1 = WorksheetFunction.Radians(ConvToDecDeg(varLon1)) * -1
Lon2 = WorksheetFunction.Radians(ConvToDecDeg(varLon2)) * -1
D = varDist * WorksheetFunction.Pi / 60 / 180
If Sin((Lon2) - (Lon1)) < 0 Then
CalcTrueCourse = WorksheetFunction.Acos((Sin(Lat2) _
- Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _
* 180 / WorksheetFunction.Pi
Else
CalcTrueCourse = 2 * WorksheetFunction.Pi _
- WorksheetFunction.Acos((Sin(Lat2) - Sin(Lat1) _
* Cos(D)) / (Sin(D) * Cos(Lat1))) _
* 180 / WorksheetFunction.Pi
End If
End Function
I can post the other dependant functions if required or email the spreadsheet.