Correlation Formula in Access

[SimonIsCool]

Hey I'm rather new to VBA so I'm not exactly adept at error-proofing, so forgive me for any blatantly stupid errors in my code.

Well anyways, i need to write a correlation function in access. I found one and modified it a little, but it won't compile. I am writing it in a new form in Access and it gives me an "invalid qualifier" error every time i call DataOne or DataTwo.

Here's the code:

Public Function Correlation(ByRef DataOne() As Single, ByRef DataTwo() As Single) As Single
Dim AveOne, SDOne, AveTwo, SDTwo, SumOne, SumTwo As Single
Dim DifferenceOne, DifferenceTwo As Single

SumOne = 0
SumTwo = 0
DifferenceOne = 0
DifferenceTwo = 0
AveOne = 0
AveTwo = 0
SDOne = 0
SDTwo = 0

Dim i As Integer

For i = 0 To DataOne.GetLength(0) - 1
SumOne = SumOne + DataOne(i)
SumTwo = SumTwo + DataTwo(i)
Next

AveOne = SumOne / (DataOne.GetLength(0))
AveTwo = SumTwo / (DataTwo.GetLength(0))

For i = 0 To DataOne.GetLength(0) - 1
DifferenceOne = DifferenceOne + (DataOne(i) - AveOne) ^ 2
DifferenceTwo = DifferenceTwo + (DataTwo(i) - AveTwo) ^ 2
Next

'Calculate the standard deviation
SDOne = Math.Sqrt(DifferenceOne / (DataOne.GetLength(0) - 1))
SDTwo = Math.Sqrt(DifferenceTwo / (DataTwo.GetLength(0) - 1))

For i = 0 To DataOne.GetLength(0) - 1
Correlation = Correlation + (((DataOne(i) - AveOne) / SDOne) * ((DataTwo(i) - AveTwo) / SDTwo))
Next

Correlation = Correlation * (1 / (DataOne.GetLength(0) - 1))

End Function

Any help or pointers would be greatly appreciated. My main goal is to get the code to compile, as the "invalid qualifier" error is completely stumping me as DataOne and DataTwo are arguments of the function.
Edit/Delete Message

[westconn1]

are the passed values to the function singles?

pete

[SimonIsCool]

Yes they are, but i dont think that's the problem because the function won't even compile, much less take input.

[westconn1]

data on and two are probably user types, so you need to look back where you got the code to see if the user typres are listed there, and maybe they are arrays if i passed them as arrays it ran into the function.

pete

[SimonIsCool]

Well i got the function compiling, but it doesnt work. I'm trying to use access columns as my input.

I'm calling correlation in a form for the control source of a text box:

"=correlation([Table Name1]![Column Name],[Table Name1]![Other Column Name])"

Where all the elements in the columns are singles. However, i am very noob at this, so maybe i'm inputting wrong because i get the #NAME error every time i run the function.

Essentially what i want to do is input 2 access columns into the function and have it spit out correlation as a single. That's all I need. DataOne and DataTwo are arbitrary parameters to store the columns i suppose, but i know very little about VBA. Thanks alot for your help.

And btw, this is very noob, but where do I write the function so that I can call it from anywhere in my database? I wrote it in a form right now, but I can't call it from anywhere else.

Sorry guys

[SimonIsCool]

Yep, it seems like my parameters are wrong, because when i test it, it says: "Compile error: type mismatch: array or user defined type expected"

In that case though... how can i fix this function so that it takes access columns in the format [column1] and [column2]?

[westconn1]

put it in a module as a public function, but i am unable to help further with access, though maybe i could get it to work in excel, perhaps i will look again tomorrow

pete