434,930 Members | 1,487 Online
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

# Undefined function - trigonometry

 P: 78 I have the following functions: Expand|Select|Wrap|Line Numbers Option Compare Database Option Explicit Public Function CalculateDistance(Lat1, Lon1, Lat2, Lon2) As Double Dim a, b, c, x As Double Const PI = 3.14159265358979 Const RadiusEarth = 3958.75587 Const MtNM = 0.868976242 ' 1 Degree is 69.096 miles, 1 mile is 1609.34 m a = Cos(Lat1 * PI / 180) * Cos(Lat2 * PI / 180) * Cos(Lon1 * PI / 180) * Cos(Lon2 * PI / 180) b = Cos(Lat1 * PI / 180) * Sin(Lon1 * PI / 180) * Cos(Lat2 * PI / 180) * Sin(Lon2 * PI / 180) c = Sin(Lat1 * PI / 180) * Sin(Lat2 * PI / 180) x = a + b + c If x >= 1 Or x <= -1 Then CalculateDistance = 0 Else CalculateDistance = Acos(x) * RadiusEarth * MtNM End If End Function     Public Function Acos(x) Acos = Atn(-x / Sqr(-x * x + 1)) + PI / 2 End Function   When I try to run, it's coming up as undefined. This was orginally an Excel function, slightly modified to work in Access. I can't figure out what's wrong with it. Anyone see the error? May 14 '08 #1
8 Replies

 Expert 2.5K+ P: 3,532 You've got two Option statements; you need to delete one of these. I don't think this alone could be causing your problem, but I'd try that first. Access does some very strange things when a code module doesn't flow normally, i.e. multiple Option statements, Subs withour End Subs, and so forth. Where do these functions reside? They should be in a standard module. What is the name of the module? Naming a module the same as a function befuddles the Access gnomes as well. Linq ;0)> May 14 '08 #2

 Expert 2.5K+ P: 2,653 Hi, kpfunf. The code is ok. Check your references (VBA window Tools>References) list for missing entries. Regards, Fish May 14 '08 #3

 P: 78 Not sure what entries to look for, but I did add Excel. What other ones would I need? May 14 '08 #4

 P: 78 Hah, I read FishVal's post without seeing missingling's. I DID have the module named the same as the function. Great lesson. Works now. May 14 '08 #5

 Expert Mod 2.5K+ P: 2,545 Hi kpfunf. An observation - in VBA the line Expand|Select|Wrap|Line Numbers Dim a, b, c, x As Double does not declare all the variables to be the same type. It is actually declaring three variants, a, b and c, as no explicit type has been specified for them, and one Double, x. The VBA interpreter will recognise that your calculations return floating-point values, and typecast the variants accordingly. I am not sure that double-precision will be used, however. It would be for the best to declare the four variables explicitly: Expand|Select|Wrap|Line Numbers Dim a as Double, b as Double, c as Double, X as Double Even if this changes nothing for this function it is something to consider in future functions where the use of variant types may introduce subtle errors that are difficult to find. -Stewart May 14 '08 #6

 Expert 2.5K+ P: 3,532 Glad we could help! You'd also do well to heed Stewart's advice about declaring each variable explicity. As Stewart said, Access is pretty tolerant about typecasting variants, but it's always better to tell Access exactly what you have in mind, especially considering the kind of calculations you're doing here! Good luck with your project. Linq ;0)> May 14 '08 #7

 P: 78 Thanks Stewart and missingling, good suggestion. May 15 '08 #8

 Expert Mod 15k+ P: 31,492 Just a comment on the Option lines : These are added for you automatically by options you've selected (Tools / Options). Option Explicit reflects the option to "Require Variable Declaration" and is extremely highly recommended. Option Compare Database reflects your sorting selection in Access itself. Neither is incorrect and having both, as long as this matches your requirements, is no problem. May 16 '08 #9