Undefined function - trigonometry

[kpfunf]

I have the following functions:
[code=vb]
Option Compare Database
Option Explicit
Public Function CalculateDistan ce(Lat1, Lon1, Lat2, Lon2) As Double
Dim a, b, c, x As Double
Const PI = 3.1415926535897 9
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
CalculateDistan ce = 0
Else
CalculateDistan ce = Acos(x) * RadiusEarth * MtNM
End If
End Function


Public Function Acos(x)
Acos = Atn(-x / Sqr(-x * x + 1)) + PI / 2
End Function
[/code]

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?

[missinglinq]

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

[FishVal]

Hi, kpfunf.

The code is ok. Check your references (VBA window Tools>Reference s) list for missing entries.

Regards,
Fish

[kpfunf]

Not sure what entries to look for, but I did add Excel. What other ones would I need?

[kpfunf]

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.

[Stewart Ross]

Hi kpfunf. An observation - in VBA the line

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:

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

[missinglinq]

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

[kpfunf]

Thanks Stewart and missingling, good suggestion.

[NeoPa]

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.