Access Hacks

Hack #61. Use Excel Functions Inside Access

Expose powerful functions available in Excel to your Access application.

Excel has many powerful built-in functions for such things as financial and statistical analysis. If you want to do the same type of analysis in Access, you can do one of the following three things: purchase an off-the-shelf code solution, write your own code for analysis, or use automation to tap into Excel's functions from inside Access. This hack shows you how to tap into Excel via automation and use spreadsheet functions, saving you time and money over the other options.

This hack involves Access working hand in hand with Excel, so you need to make sure Excel is installed on the machine on which your database will be running. This is a safe assumption in most corporate environments.

A Simple Excel Function

Excel's FV (future value) function calculates the value of an investment at some time in the future based on periodic, constant payments and on a constant interest rate. The following VBA function takes the same parameters as Excel's FV worksheet function and returns the same result as if you were using the future value function right in Excel:

  Public Function FV(dblRate As Double, intNper As Integer, _ 
    			  dblPmt As Double, dblPv As Double, _ 
				  intType As Integer) As Double
	 Dim xl As Object
     Set xl = CreateObject("Excel.Application")
     FV = xl.WorksheetFunction.FV(dblRate, intNper, dblPmt, dblPv, intType)
     Set xl = Nothing
 End Function

The WorksheetFunction property of ...