Using the Excel Trend function in Access

[Michael]

According to the MS knowledge base it is possible to call
the Excel statistical functions from within Access. I am
interested in calling the TREND function (linear
regression). The difficulty I have is that the info
provided is a little short of detail on the specific
function I am interested in and object oriented
propramming is still a little new for me. What I want to
do is to pass two arrays and a single variable to TREND
and get back a result (in the same way it functions in
Excel) in the form TREND(array1,array2,var1). The code I
have written looks like this:

Function Trend()
Dim objExcel As Excel.Application
Dim Arg1(10), Arg2(10), constarg As Integer
constarg = Log(10)
For x = 1 To 10
Arg1(x) = x * 100
Arg2(x) = Log(x)
Next
Set objExcel = CreateObject("Excel.Application")
MsgBox objExcel.WorksheetFunction.Trend(Arg1, Arg2,
constarg)
objExcel.Quit
Set objExcel = Nothing
End Function

For the moment I have used arg1, arg2 and constarg as
dummy data, just to test the function. However I can't
get past error messages. The error message I get
is "Unable to get the Trend Property of the
WorkSheetFunction Class". I would be really grateful for
any advice or help

Cheers

 

[M.L. Sco Scofield]

Interesting, however, know issue. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;213690.

First, I'm guessing that you don't have Option Explicit set. (Your code
wouldn't compile until I added a Dim x as Integer.) Good habit to get into.
Check "Require Variable Declaration" at Tools/Options/Editor tab. It won't
add it for existing modules, (you have to add it manually for existing
modules,) but it will add it for all new modules.

That said, applying the info in KB 213690 was a bit of a challenge. Give
this a try:

Function Trend()

Dim objExcel As Excel.Application
Dim Arg1(10) As Double
Dim Arg2(10) As Double
Dim constarg As Double
Dim x As Integer
Dim result As Variant

constarg = Log(10)

For x = 1 To 10
Arg1(x) = x * 100
Arg2(x) = Log(x)
Next

Set objExcel = CreateObject("Excel.Application")

result = objExcel.WorksheetFunction.Trend(Arg1, Arg2, constarg)
MsgBox result(1)

objExcel.Quit
Set objExcel = Nothing

End Function

Good luck.

Sco

 

[Guest]

Thanks for your help and suggestions. That worked a
treat!!!

Cheers

-----Original Message-----
Interesting, however, know issue. See
http://support.microsoft.com/default.aspx?scid=kb;en- us;213690.

First, I'm guessing that you don't have Option Explicit set. (Your code
wouldn't compile until I added a Dim x as Integer.) Good habit to get into.
Check "Require Variable Declaration" at

Tools/Options/Editor tab. It won't

 

[M.L. Sco Scofield]

You're most welcome.

Sco

Thanks for your help and suggestions. That worked a
treat!!!

Cheers

Tools/Options/Editor tab. It won't