1. Public Function xlForeCast() As Double
2.
3. Dim MyDate As Integer 'Will be the point for which you are forecasting, in this case 2007
4. Dim MyRange() As Variant 'Will be the independent element of the forecast function
5. Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
6. Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
7. Dim db As DAO.Database
8. Dim rs1 As DAO.Recordset
9. Dim ls As Integer 'Temp variable to count the rows in the list
10.
11.
12. Set db = CurrentDb()
13. Set rs1 = db.OpenRecordset("qryGetHistory") 'Opens the query that feeds the data
14.
15. With rs1
16. .MoveFirst
17. .MoveLast
18. ls = .RecordCount
19. .MoveFirst
20. MyArray() = .GetRows(ls) 'Populate the temporary array with the query results
21. End With
22. 'Split the required data into two arrays, drawing from columns two and three in the query/array
23. MyRange() = Array(CInt(MyArray(1, 0)), CInt(MyArray(1, 1)), CInt(MyArray(1, 2)), CInt(MyArray(1, 3)), CInt(MyArray(1, 4)))
24. MyRange1() = Array(CInt(MyArray(2, 0)), CInt(MyArray(2, 1)), CInt(MyArray(2, 2)), CInt(MyArray(2, 3)), CInt(MyArray(2, 4)))
25. MyDate = CInt(DatePart("yyyy", "July 30")) 'Set the Desired point to forecast for
26. rs1.Close
27. Set rs1 = Nothing 'Reset the recordset, releasing memory
28. Set db = Nothing
29.
30. xlForeCast = Excel.WorksheetFunction.Forecast(MyDate, MyRange1, MyRange) 'Calls the Excel forecast function
31.
32. Erase MyArray 'Reset the Arrays to zero, releasing memory
33. Erase MyRange
34. Erase MyRange1
35. End Function
