The following two examples use early bound code. Early binding means that you
will need to set a reference to the "Microsoft Excel X.0 Object Library",
where X = 9 for Excel 2000, 10 for Excel 2002 and 11 for Excel 2003. To set
this reference, create a new module. Then click on Tools > References. Scroll
down the list until you find this object library. Place a check in it. Also,
make sure that you have a reference set to the "Microsoft DAO 3.6 Object
Library".
You can likely convert this code to late bound code (does not require a
reference to Excel), but first things first. Get your code working early
bound, where you can use Intellisense, before trying to convert it to the
more robust late bound. These examples were tested using Access 2003 with
Excel 2003.
Example # 1 uses the same values found in the Excel Help file for the
Forecast function, as hardcoded values:
Sub ForecastHardcodedTest()
On Error GoTo ProcError
' Should return 10.60725
Dim objExcel As Excel.Application
Dim Y(5) As Variant
Dim X(5) As Variant
Set objExcel = CreateObject("Excel.Application")
Y(0) = 6
Y(1) = 7
Y(2) = 9
Y(3) = 15
Y(4) = 21
X(0) = 20
X(1) = 28
X(2) = 31
X(3) = 38
X(4) = 40
MsgBox objExcel.Application.Forecast(30, Y, X)
ExitProc:
'Cleanup
objExcel.Quit: Set objExcel = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ForecastTest..."
Resume ExitProc
End Sub
To run this procedure, make sure your blinking mouse cursor is anywhere
within the procedure. Then press the F5 button.
The second example uses the same X,Y values, but this time we are going to
store them in a new table. Create a new table. Add the following three fields:
Field Name Data Type
pkDatum Autonumber (set as primary key)
YValue Number (Long Integer)
XValue Number (Long Integer)
Save the table with the name: tblForecast
Populate your table with the following values (the autonumber will increment
itself):
pkDatum YValue XValue
1 6 20
2 7 28
3 9 31
4 15 38
5 21 40
(These are the same X,Y values shown in the Excel Help file). Create the
following subroutine:
Sub ForecastTest()
On Error GoTo ProcError
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Long
Dim j As Long
Dim Y() As Variant
Dim X() As Variant
Dim objExcel As Excel.Application
Set db = CurrentDb()
Set objExcel = CreateObject("Excel.Application")
strSQL = "SELECT YValue, XValue FROM tblForecast"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
j = rs.RecordCount
ReDim Y(j)
ReDim X(j)
'Populate Arrays
With rs
For i = 0 To j - 1
Y(i) = rs("YValue")
X(i) = rs("XValue")
rs.MoveNext
Next i
End With
' For i = 0 To j - 1
' Debug.Print Y(i) & ", " & X(i)
' Next i
'Call Forecast Function
MsgBox objExcel.Application.Forecast(30, Y, X)
End If
ExitProc:
'Cleanup
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
objExcel.Quit: Set objExcel = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ForecastTest..."
Resume ExitProc
End Sub
So, the only hardcoded value that remains is the X value that we are
evaluating (30). This is easy enough to change, so that it references a
textbox in a form, or a value in a table, etc.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
