Using Excel function Forecast in Access

[Texan_Padre]

I am trying to use the Excel worksheet function "Forecast" to calculate from existing data in my database. I have successfully gotten the data points into arrays, but when I try to call the forecast function I keep getting the following error: "Unable to get the Forecast property of the WorksheetFunction class". I have imported the object library. This is on a company computer and I do not have write privileges to the C: drive. The database resides on a network drive.

Am I facing some sort of security issue?

Public Function xlForeCast() As Double

Dim MyHeight As Variant 'Will be the point for which you are forecasting, in this case height
Dim MyRange() As Variant 'Will be the independent element of the forecast function
Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim ls As Integer 'Temp variable to count the rows in the list
Dim x As Object
Set x = Reports.Item("Historical Data").Controls("Label85")
MyHeight = CDec(x.Caption) 'Set the Desired point to forecast for

Erase MyArray 'Reset the Arrays to zero, releasing memory
Erase MyRange
Erase MyRange1
Set rs1 = Nothing 'Reset the recordset, releasing memory
Set db = Nothing

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qry_ProStar_5th_Wheel") 'Opens the query that feeds the data

With rs1
.MoveFirst
.MoveLast
ls = .RecordCount
.MoveFirst


MyArray() = .GetRows(ls) 'Populate the temporary array with the query results
End With
'Split the required data into two arrays, drawing from columns two and nine in the query/array
MyRange() = Array(MyArray(1, 0), MyArray(1, 1), MyArray(1, 2), MyArray(1, 3), MyArray(1, 4), MyArray(1, 5), MyArray(1, 6), MyArray(1, 7), MyArray(1, 8), MyArray(1, 9), MyArray(1, 10))
MyRange1() = Array(MyArray(8, 0), MyArray(8, 1), MyArray(8, 2), MyArray(8, 3), MyArray(8, 4), MyArray(8, 5), MyArray(8, 6), MyArray(8, 7), MyArray(8, 8), MyArray(8, 9), MyArray(8, 10))
rs1.Close
Set rs1 = Nothing 'Reset the recordset, releasing memory
Set db = Nothing

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlForeCast = xlApp.WorksheetFunction.Forecast(MyHeight, MyRange1, MyRange) 'Calls the Excel forecast function

xlApp.Quit


Erase MyArray 'Reset the Arrays to zero, releasing memory
Erase MyRange
Erase MyRange1
End Function

[June7]

I tested the worksheet function call in Access and it works for me. I would not have thought there would be security issue.

What do you mean by 'imported' the object library - you set VBA reference to Microsoft Excel 12.0 Object Library?