What's going on with this Excel formula?

General Questions
#1

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.

#2

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.

w.

#3

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.

I got the formula from http://williams.best.vwh.net/avform.htm.

#4

Is there a division by zero going on?

Does the original function work for both > 0 and < 0 ? (i.e., only fails when = 0)?

Brian

#5

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.

#6

I think possibly the SIN of the distance is getting rounded to zero.

And yeah, the original only failed at zero, but that particular zero isn’t getting used in the formula itself.

#7

Well sin(0) = 0
Does it fail if the distance is LESS than 60nm? If so, then it is probably a round off issue.
What is D declared as?
Brian

#8

This could be the reason. Try changing all of the DIM … AS SINGLE to DIM … AS DOUBLE.

w.

#9

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.)

I think I’m going to have to do a bit of study.

#10

I’ve solved the problem, or rather, I’ve used a different formula that doesn’t require a distance input and that doesn’t seem to have this problem.

#11

Makes sense, that’s why I asked why the formula required a distance input.

w.

#12

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.)

#13

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.

#14

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

10:00.0S 133:00.0E
11:00.0S 133:00.0E

ConvToDeDeg would just make it

10.0S 133.0E
11.0S 133.0E

#15

Oh, and south positions are negative and the “S” and “E” wouldn’t be there so,

-10.0 133.0
-11.0 133.0