Problem using Worksheet Functions???

[ejc00]

I need to do natural log (LN) and Correlation (Correl) calculations in Access... however, when i try to use them in queries the way i use functions like Sum, i get the error message: "Undefined function 'LN' in expression."

in Access help, these functions are listed as Worksheet functions. is there any way to enable these in Access (2000)? or is there an easy way to calculate these?

thanks,
evan

 

[mscraig]

As best I can tell, those function are not allowed in Access. You might try simply linking to the worksheet and perform your calcs in the excel worksheet. The link is dynamic.

 

[ejc00]

thanks for the tip...

through an excel newsgroup, i've figured out my dilemma somewhat... here is the code i'm using:


Public Function Corr(x As Range, y As Range)

Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application&quot

Corr = xl.Correl(x, y)

xl.Quit ' Very important.
Set xl = Nothing

End Function


where "Correl" is my Excel function. the invocation of the function is working correctly, it's my range that isn't working out exactly as planned.

thanks again,
ejc

 

[MichaelRed]

did you add the reference to the excel library to your app?

MOST of the Excel procedures are available, the ones with "range" arguments need arrays to be placed in the arg position.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over