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.
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.
Well, I messed with it and can’t get it to work at all. What is the “varDist” variable? Seems like if you want the course from point A to point B, that’s all the information you need.
It needs the latitude and longitude of each position plus the distance, all in radians (i.e., the angular distance.) It will only work out the course, the distance is a seperate formula, though there is another formula that will do both course and distance.
There is no division by zero as far as I can see. The longitude is only required to decide which of the two formula’s to use, so the zero should just be telling it to use the second formula.
Right I’ve narrowed it down a little. The problem only occurs when the distance is 60nm, and the longitudes are the same (i.e., the course is either north or south.) It occurs without the longitudes in the formula. So if I give it two latitudes of say 10.0S and 11.0S and tell it the distance is 60nm (which implies a course of south,) it doesn’t like it, but if I increase the distance, it works ok. It also works if I make the lats 10.1S and 11.0S with a distance coincident with a course of south.
So perhaps there is some dividing by zero going on. Unfortunately I don’t fully understand the formula, mainly because I haven’t done trig for 15 years and I can’t be bothered learning it again.
It fails only when the distance is exactly 60 or 120 but is ok with a distance of 180.
D isn’t declared as anything yet. Part of the problem is that not only do I not understand the formula, I’m also new to VBA and don’t always understand that either (for example, I’m not entirely clear on what to use the various different number types for, single, double, long, etc.)
The spreadsheet has a separate distance column. Because the distance is already supplied, I used the formula that has a distance input. It didn’t really matter which I used (apart from the glitch.)
You’ve got a workaround so maybe you don’t care anymore, but I was trying to troubleshoot this and found that ConvToDecDeg is undefined. Is that another function that you wrote yourself? What units are the parameters for lat & long in, and why are you converting them to degrees then to radians? Not that I think that’s a problem, just don’t understand what you’re doing. I checked your reference site and didn’t see that in their formula.
ConvToDecDeg was written by me. It converts a latitude or longitude from degrees minutes and decimal minutes (DDMM.mm) as generally used by GPS units to decimal degrees (DD.dddd) which is easier to work with. It also didn’t quite work properly, as it didn’t handle single digit degrees. I’ve fixed that by changing the latitude/longitude input format to DD.MM.mm which forces the cell to be text and maintains leading zeros, allowing my simple formula, that made use of the LEFT() and MID() functions, to work.
Another quick question while you are here, is there a way to keep leading zeros without making the cell text? I know you can use format to display leading zeros but the underlying number doesn’t have them.
Anyway, if you’re still interested in trouble shooting it, the kind of position it wasn’t working for was say