A good example of this is the NETWORKDAYS() function in Excel. This function returns the number of working days between two dates. This is a common requirement whether the data is in Excel or Access. Unfortunately, Access does not have an equivalent function for the same purpose. Instead, Access users are forced to create custom functions to do the same thing.
If you have not tried it before, creating a custom function in Access to mimic the functionality of the NETWORKDAYS() Excel function is not easy. There are a number of Access NETWORKDAYS() code examples available online. As it turns out, creating a duplicate Excel function in Access is not always necessary. We can execute some Excel functions through automation. The following code example shows how we can execute the Excel NETWOKDAYS() function from within Access.
Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long '8/3/2017 'firstname.lastname@example.org 'Uses Excel's NetWorkdays() function Dim xl As Object Set xl = CreateObject("Excel.Application") NetWorkdays = xl.WorksheetFunction.NETWORKDAYS(Format(StartDate, "yyyy-mm-dd"), _ Format(EndDate, "yyyy-mm-dd")) Set xl = Nothing End Function
What the above code does is instantiate an Excel object and then use the WorksheetFunction method to execute the Excel NETWORKDAYS() function.
Users can use this technique to execute some built-in Excel functions if they can't find an equivalent Access function to do the same thing. I hope you find it useful.