Have you ever needed to use an Excel function within one of your databases, or other application. Below is a generic examples of how you can call just about any Excel function using VBA to extend your database’s functionalities even further.

Late Binding

'---------------------------------------------------------------------------------------
' Procedure : GetXLWkSHtFuncVal
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Execute an Excel Worksheet Function from MS Access
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Jan-31             Initial Release
'---------------------------------------------------------------------------------------
Function GetXLWkSHtFuncVal()
   Dim xlApp      As Object
On Error GoTo Error_Handler

   Set xlApp = CreateObject("Excel.Application")
   xlApp.Visible = False   'Control whether or not Excel should be visible to
                           'the user or not.
   
   'This is a generic example using the NormInv(), but you can do the same with just
   'about any other Excel Worksheet function.
   GetXLWkSHtFuncVal = xlApp.WorksheetFunction.NormInv(0.25, 4, 1)
   
   xlApp.Quit           'Close the instance of Excel we create

Error_Handler_Exit:
   On Error Resume Next
   xlApp.Visible = True
   Set xlApp = Nothing
   Exit Function

Error_Handler:
   MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
   Err.Number & vbCrLf & "Error Source: GetXLWkSHtFuncVal" & vbCrLf & "Error Description: " & _
   Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

You could technically even get away with simply doing:

Dim dNormInv as Double
dNormInv = CreateObject("Excel.Application").WorksheetFunction.NormInv(0.25, 4, 1)

but personally, I do not like this type of approach in general. I like having proper error handling, and when dealing with objects, I like creating them and keep them in memory to reuse, rather then continuously opening and closing them.
 

Early Binding

If you prefer Early binding, then you can actually access the function directly by doing something like:

Function GetXLWkSHtFuncVal()
   'This approach requires a reference be set to Microsoft Excel XX.X Object Library
   GetXLWkSHtFuncVal = Excel.WorksheetFunction.NormInv(0.25, 4, 1)
End Function

If you check with your task manager, you will see a hidden Excel instance open and then close because of this call. So via Early binding Access does all the object management should you not wish to do all the Dim declarations and setting …

Personally, I still do it though, I like to have control of what is going on. By doing it myself I can also create a persistent Excel Object that I reuse. Leaving it to Access it seems to continually create and dispose of the Excel instance with each call, so less than optimal.

Function GetXLWkSHtFuncVal()
    'This approach requires a reference be set to Microsoft Excel XX.X Object Library
    Dim oExcel                As Excel.Application
    
    Set oExcel = New Excel.Application
    With oExcel
        .UserControl = False
        .ScreenUpdating = False
        .Visible = False
        GetXLWkSHtFuncVal = .WorksheetFunction.NormInv(0.25, 4, 1)
    End With
    oExcel.Quit
    Set oExcel = Nothing
End Function

Note that normally, I would use Self-Healing Object Variables (SHOV) and only .Quit Excel when I would close the database itself. This way I only need create the Excel instance once for the duration of the database session and this for both Early and Late Binding.

Below is an Early Binding example of what I’m talking about:

'Early Binding Self-Healing Object Variable example
'This approach requires a reference be set to Microsoft Excel XX.X Object Library
Private pExcel                As Excel.Application

Public Function oExcel() As Excel.Application
On Error GoTo Err_Handler
    
    If pExcel Is Nothing Then
        Debug.Print "*** Setting oExcel ***"
            Set pExcel = New Excel.Application
            Set pExcel = CreateObject("Excel.Application")
    End If
    Set oExcel = pExcel

Exit_Procedure:
    DoEvents
    Exit Function

Err_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Property Get oExcel" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Exit_Procedure
End Function

Public Sub oExcel_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pExcel = Nothing
End Sub

Public Sub oExcel_Terminate()
    oExcel.Quit
    oExcel_Clear
End Sub

Function GetXLWkSHtFuncVal()
    With oExcel
        .UserControl = False
        .ScreenUpdating = False
        .Visible = False
        GetXLWkSHtFuncVal = .WorksheetFunction.NormInv(0.25, 4, 1)
    End With
End Function

and as part of the closing of my database I would run oExcel_Terminate to terminate the oExcel hidden instance.