Excel Functions in Access

[NeoPa]

Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as standard, in Access.
A particular issue I had recently (and I know others have come across this recently too) is rounding up and down. I know the Excel ROUNDUP() function rounds away from zero rather than upwards specifically, but is still useful in most circumstances.

To allow an Access database to use Excel (and by extension other Office libraries) you need to make the library available (this is on a database level rather than an Access installation level so be aware) simply :

1. Open (or switch to) the VBA Window (Alt-F11).
2. From the menus select Tools / References...
3. Find the relevant library (in this specific case it will be Microsoft Excel 9.0 Object Library for Access 2000 which I'm using here, or whichever is right for your current version of Office).

While you're doing that you'll probably notice a whole bunch of other available references (libraries) which are available to link to.

This will save reproducing a bunch of functions which you may already be familiar with but find, to your disappointment, are not natively available in Access.

When I originally set this up I did some testing badly and thought that the Excel functions were available to SQL in a database that had the Excel library referenced. I subsequently discovered that not to be the case.

However, Public functions in your database are accessible to Jet SQL so if you have a requirement to implement an Excel function it is still possible to do so by the expedient of creating an encapsulating function (defined as Public in your database) which, itself, calls the Excel function. The VBA code in the database CAN access the Excel libraries. It is only Jet SQL that cannot.

Posted by Scott Price

This is a specific example of an Excel function; Forecast(). Called from a VBA code module in Access. (Developed using Access 2003).

Public Function xlForeCast() As Double
 
Dim MyDate As Integer 'Will be the point for which you are forecasting, in this case 2007
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
 
 
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qryGetHistory") '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 three in the query/array
MyRange() = Array(CInt(MyArray(1, 0)), CInt(MyArray(1, 1)), CInt(MyArray(1, 2)), CInt(MyArray(1, 3)), CInt(MyArray(1, 4)))
MyRange1() = Array(CInt(MyArray(2, 0)), CInt(MyArray(2, 1)), CInt(MyArray(2, 2)), CInt(MyArray(2, 3)), CInt(MyArray(2, 4)))
MyDate = CInt(DatePart("yyyy", "July 30")) 'Set the Desired point to forecast for
rs1.Close
Set rs1 = Nothing 'Reset the recordset, releasing memory
Set db = Nothing
 
    xlForeCast = Excel.WorksheetFunction.Forecast(MyDate, MyRange1, MyRange) 'Calls the Excel forecast function
 
Erase MyArray 'Reset the Arrays to zero, releasing memory
Erase MyRange
Erase MyRange1
End Function