By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
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
  1. Option Compare Database
  2. Option Explicit
  3. Public Function CalculateDistance(Lat1, Lon1, Lat2, Lon2) As Double
  4. Dim a, b, c, x As Double
  5. Const PI = 3.14159265358979
  6. Const RadiusEarth = 3958.75587
  7. Const MtNM = 0.868976242
  8. ' 1 Degree is 69.096 miles, 1 mile is 1609.34 m
  9. a = Cos(Lat1 * PI / 180) * Cos(Lat2 * PI / 180) * Cos(Lon1 * PI / 180) * Cos(Lon2 * PI / 180)
  10. b = Cos(Lat1 * PI / 180) * Sin(Lon1 * PI / 180) * Cos(Lat2 * PI / 180) * Sin(Lon2 * PI / 180)
  11. c = Sin(Lat1 * PI / 180) * Sin(Lat2 * PI / 180)
  12. x = a + b + c
  13. If x >= 1 Or x <= -1 Then
  14. CalculateDistance = 0
  15. Else
  16. CalculateDistance = Acos(x) * RadiusEarth * MtNM
  17. End If
  18. End Function
  21. Public Function Acos(x)
  22. Acos = Atn(-x / Sqr(-x * x + 1)) + PI / 2
  23. 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
Share this Question
Share on Google+
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.

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

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

Post your reply

Sign in to post your reply or Sign up for a free account.