Using Excel Functions from within Access

Using Excel Functions from within Access

There are times when Access doesn't have the required functions built-in. In such cases, you have several options:

Create the function yourself to be used from within Access (or else use a function that somebody else has created - this might be a commercial library add-in);
Use an existing function from another Office program, such as Excel, that meets your requirements.

Using Excel, or any other Office program, has several implications. Firstly, if Excel isn't present on the user's machine the function will fail, and even if Excel is there, if you are using Early Binding and setting a reference to the Excel Object Library in your database, you need to ensure that the versions of Excel are the same. Secondly, there will be a noticeable performance loss.

When using Excel, you can either use Early Binding or Late Binding. In the former, which is faster, you ensure that a reference to Excel is set in the database. This allows you to also use the Object Browser and Intelli-sense, making the coding easier. Using Late Binding makes the process slower still, and should be used if you're not too sure which version of Excel will be on the user's computer.

One thing that you will have to ensure is that there is preventative checking of values and datatypes, rather than using error handling as you can with native Access functions.

Below are various Excel functions, using Early Binding. Some of the functions won't work using Late Binding, apparently if they require the use of a function from the Excel Analysis ToolPak:

Function fXLRoman(intIn As Integer, Optional bytForm As Byte) As String
'   Uses the XL function ROMAN to convert an Arabic number to its Roman Equivalent.
'   Accepts:
'   intIn - an Integer value between 0 and 3999
'   strForm - indicating how concise the return value is to be
'   Returns:
'   a string containing the converted number
    On Error GoTo E_Handle
    Dim objXL As Excel.Application
    Set objXL = CreateObject("Excel.Application")
    If IsMissing(bytForm) Then bytForm = 0
    If intIn < 1 Or intIn > 3999 Then
        fXLRoman = intIn
    Else
        fXLRoman = objXL.WorksheetFunction.Roman(intIn, bytForm)
    End If
fExit:
    objXL.Quit
    Set objXL = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Top

Function fXLYield(dtmSettlement As Date, dtmMaturity As Date, dblRate As Double, dblPR As Double, dblRedemption As Double, bytFrequency As Byte, bytBasis As Byte) As Double
'   Uses the Excel function YIELD to return the yield on a security that pays periodic interest
'   Accepts:
'   dtmSettlement - The security's settlement date
'   dtmMaturity - The security's maturity date
'   dblRate - The security's annual coupon rate
'   dblPR - The security's price per $100 face value
'   dblRedemption - The security's redemption value per $100 face value
'   bytFrequency - the frequency of the payments per year
'   bytBasis - The type of day count to use
'   Returns:
'   The yield
    On Error GoTo E_Handle
    Dim objXL As Excel.Application
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (objXL.Application.LibraryPath & "\Analysis\atpvbaen.xla")
    objXL.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
    fXLYield = objXL.Application.Run("atpvbaen.xla!yield", dtmSettlement, dtmMaturity, dblRate, dblPR, dblRedemption, bytFrequency, bytBasis)
fExit:
    objXL.Quit
    Set objXL = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Top

Function fXLDays360(dtmStart As Date, dtmEnd As Date, Optional blnMethod As Boolean) 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
    fXLDays360 = objXL.WorksheetFunction.Days360(dtmStart, dtmEnd, blnMethod)
fExit:
    objXL.Quit
    Set objXL = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Top

Last modified at 06/06/2006 14:54:29