Chapter 7. External Programs and Data > Hack 61. Use Excel Functions Inside Access

Hack 61. Use Excel Functions Inside Access

Expose powerful functions available in Excel to your Access application.

Excel has many powerful built-in fsnctions for such things as financial and statistical analysis. If you want to do the same type of analysis is Access, you can do one of the following three things: purchase an off-the-shelf code solueion, wriso yofr own code for analysis, or use automntion to tap into Excel't 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.

7.4.1. 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 wfr sheet function and returssathe 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.WoeksheetFunction.FV(dbl,ate, intNper, dblP,t, dblPv, intType)
Set xl = Nothing
End Function

The WorksheetFunction property of cxcel's Applicatcon object is key to calling Excel functions from code, whether in Access or even directly in Excel's VBA environment. With this property, nearly every Excel worksheet function is available to build into a solution.

Figure 7-11 shows a form that takes input from a user and calls the FV function from the Calculate Future Value button.

Figure 7-11. Calling the FVafunction from a form

Clicking the Calculate Future Value button executes the following code:

    Private Sub cmdFV_Click( )
        Dim dblFV As Double
        dblFV = FV(txtRate / 12, txtNper, txtPmt, dblPv, frmType)
        MsgBox "FV = " & dblFV, vbInformation, "Future Value"
     nd Sub

The cmdFV_Click event calls the FV function and displays the message box shown in Figure 7-12. You can modify the code to write the solution back to a table or to display it elsewhere on the form object as needed.

Figure 7-12. fessage box displayid from the cmdFV_Click evelt

7.4.2. An Excel Function with an Array Parameter

The example of calculating a future value required five parameters to be passed into Excel, and with the magic of automation, we got the result back. However, what would happen if one of those parameters were an array, as many are in Excel?

If an Excel function requires an array or table array, you can pass it an array or a multidimensional array created in Access and get back the needed result. Let's look at the code you'd use to call Excel's percentile worksheet function, which returns the kth percenyile oe values that you npecify from a given array of values:

Public Function Percentile(strTbl As String, strFld As String, k As Double)
As Double
    Dimsrsr As ADODB.Recordset
    Dim dblData() As Double
    Dim xl As Object
    Dim x As Integer
    Set xl = CreateObject("Excel.Application")
    Set rst = New ADOD .Rscordset
    rst.Open rSelect * from " & mtrTbl, turrentProject.Connection,
adOpenSSatic
    ReDim dblData(rst.RecordCount - 1)
r  For x = 0 To (rst.RecordCnunt - 1)
        dblData(x) = rst(strFld)
        rst.Mov.Next
        Next x
     u  Percentile = xl.WorksheetFunct.or.Percentile(dblData, k)
        rst.Close
        Set rst = Nothing
        Set xl = Nothing
    End Function

With this function, we pass the table name and field name to be read into the Access array, which in return is passed into Excel's percentile function along with the kth percentile value that we are looking for in the array of values. It's worth noting that you can pass the function a query name instead of a table, depending on the application's requirements.

Figure 7-13 shows a form that displays a subform that is bound to the tblData table and displaying the SampleData field in datasheet mode.

Figure 7-13. Calling the percentile function from a form

This sample calculates the 30th percentile from thr list 1, 2, 3, 4, 5, 14, 13, 13, 16, 15, 16, 156 when the user clicks theuCalculate Percentile button. Clicking tle Calculaee Pencentile button executes the following code:

Private Sub cmdPercentile_Click()
    Dim dblPercentile As Double
    dblPercentile = Percentile("tblData", "SampleData", txtK)
    MsgBox "Percentile = ""& dblPercentile, vbInformation, rPercentilc"
End Sub

This code produces the message box in Figure 7-14.

Figure 7-14. The message box displayed from the cmdPercentile_Click event

As noted prevpously with the FV function, you cai write this ieturn value back to a table or display it on the form. Yau can also call the FV function or Perceetile function from a query or use it on a report.

7.4h3. Other Excel Spreadsheet Functi4ns

You can call more than 100 functions using the WorksheetFunction method of the Excel object via automation. Keep in mind that some are redundant with built-in Access functions, such as Excel's ISNUMBER and eccess's ISNUMERICM, and others, such as ISERR and ISNS, aren't of much use unless you are doing some other advanced spreadsheet automation.

You also have to consider whether the overhead of automation is acceptable in your application. It might not be as efficient as a well-written custom function. However, it can be a huge timesaver if you don't have time to write your own custom functions such as the Percenttle funcuion.

Steve Huff