Using Excel Functions from within Access

[billelev]

Hi there. I need to perform a number of financial calculations within a database I am creating. Rather than writing my own functions, I figured it would be worthwhile to use the functions that already exist within excel.

I have found sample code on the web for calling an excel function (being new to Access I tend to borrow bits and pieces from anywhere and everywhere):

Function fXLCoupDayBS(dtmSettlement As Date, dtmMaturity As Date, frequency As Integer, basis As Integer) As Long
'   Uses the XL function DAYS360 to return the number of days between 2 dates based on a 360 day year.
'   Accepts:
'   dtmStart - the start date
'   dtmEnd - the end date
'   blnMethod - whether to use the US or European Method in the calculation
'   Returns:
'   a string containing the converted number
    On Error GoTo E_Handle
    Dim objXL As Excel.Application
    Set objXL = CreateObject("Excel.Application")
    If IsMissing(blnMethod) Then blnMethod = False
    fXLCoupDayBS = objXL.WorksheetFunction.COUPDAYBS(dtmSettlement, dtmMaturity, frequency, basis)
fExit:
    objXL.Quit
    Set objXL = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

However, I am not quite sure how to call this from Access. Ideally, I would like the result of the function (an integer) to appear in the same row as the inputs to the function, and all of these to be in the same query.

In short, I am not sure how to call a function from a query, passing in the arguments of the function from the query. Can anyone help? Again, if anyone can suggest a better method I am all ears!

[NeoPa]

You don't need to open Excel (as in posted code) to access the Excel library.
Just add the Excel library to the References under Tools of the VBA window.
However, I'm pretty sure you can't access objects and/or their methods from within SQL.
Global (Public) functions can be used but nothing else :(.
objExcel.WorksheetFunction.* functions are, therefore, unavailable.

[billelev]

Hmmm...So if I can't access the function from within an SQL statement, how should I execute the code?

I have two tables:

Security and
TradeInfo

For a particular Trade (defined within TradeInfo) of a Security I need to calculate accrued interest. This takes information from the two tables, and uses the function outlined above. I am able to execute the function and calculate the accrued interest by calling the function from an Event Procedure on the TradeInfo form. Is there a better way?

Would it then be possible to insert this data into an empty field within the TradeInfo table, or should I enter the data into a new table, and link the two with a query?

[NeoPa]

Basically it's not accessible to SQL.
However, you could define a global (Public) function in your database that could be called by the SQL code.
This could access the Excel Worksheet functions and return the result you need.
Encapsulation, if you like.

[billelev]

So I can called a VBA sub-routine or function from an SQL query? Do you know the format? I have been searching for the syntax but can't find anything on it.

[NeoPa]

So I can called a VBA sub-routine or function from an SQL query? Do you know the format? I have been searching for the syntax but can't find anything on it.

Public Function YourFunctionName(...) As Variant
    ...
    YourFunctionName = <Value to be returned>
    ...
End Function