Trig Calculation in VBA code

[Dan Caya]

Hi Guys,
I am attempting to create a flight planning database in Access. I have developed an Excel program which works great, however the growth of the data requires that we build a program in Access. I have a Trig formula which calculates a wind correction angle. I am certain that I need to define this as a function, however, Access does not define DEGREES or RADIANS
Any help with the following formula would probably help to decrease my rapid aging process

X = [Track]+DEGREES(ASIN([Speed]/[TAS]*SIN(RADIANS([Track]-[Wind]+180))))

..where...
Track = 138
Speed = 55
TAS = 270
Wind = 350

Answer should be : 132

In Excel this calculates in less than a heartbreat

Dan

 

[Andrew Fergus]

Hi Dan

I created an add-in for Access some time ago that gives you access to the Excel trig functions via VBA. You can download the dll file from here:
http://www.accessdata.co.nz/samples.htm

This only works with VBA but you can use it to create your own trig functions, like this:

Public Function Degrees(InputVal As Double) As Double

Dim ATF As XLTrigFuncs.XLFunctions
Set ATF = New XLTrigFuncs.XLFunctions

Degrees = ATF.AngleDegrees(InputVal)

Set ATF = Nothing

End Function

Then you can use the 'Degrees' function in a query, like so :
Degrees(MyNumber)

There are 13 different trig functions you can call on - as you type the "ATF." part you should then see a list pop up of the available trig functions. You will need to create a separate function for each function you want to use much like the example I provided. You could also expand the function to check for errors (null strings, bad values etc) before passing the value into the function to avoid any unexpected errors.

Given this dll calls on the Excel equivalent functions, it isn't the speediest method but it works. There are also some instructions on the webpage for registering the dll file.

Just post back if you get stuck.

HTH, Andrew

 

[Dan Caya]

Thanks Andrew,
I'll give this a try. It may be a few days before I know if I can get it working. I start a 14 day flying shift in the morning. It can get too hectic to sit at the computer.

Dan

 

[Andrew Fergus]

No worries.

Crikey - a 14 day shift is pretty impressive! Just post back when you have some progress - I will still be here!

Andrew

 

[Norie]

Dan

I know Access doesn't have DEGREES or RADIANS functions.

But could not use the inbuilt trig functions.

I know they aren't as useful as the ones in Excel but surely with simply maths you could use them.

 

[Dan Caya]

Hi Andrew, I downloaded your code at home and was just going to start working with it before I left. I'm now not there. Where I am (2000 Km away) is resistant to downloads and all other such nonsense.
After much grumping, I added the following as functions to my modules. I've posted them so that others can access (no pun intended) them for future use. Still fail to grasp why MS can't make Excel functions available to Access.

Function ArcSin(X As Double) As Double
' Inverse Sine

If X = 1 Then
ArcSin = PI() / 2
ElseIf X = -1 Then
ArcSin = -PI() / 2
Else
ArcSin = Atn(X / Sqr(-X * X + 1))

End If
End Function


Function Deg2Rad(X As Double) As Double
' Degrees to radians
Deg2Rad = X / 180 * PI()
End Function

Function PI() As Double
PI = Atn(1) * 4
End Function

Function Rad2Deg(X As Double) As Double
' Radians to Degrees
Rad2Deg = X / PI() * 180
End Function

The formulas now work great.

P.S. 14 days 24/7 - 14 off ***** Ahhhh!!! the life of an AirAmbulance Pilot