Recipe 12.3 Use Excel's Functions from Within Access

12.3.1 Problem

Excel offers an amazing array of statistical, analytical, and financial functions that you'd like to be able to use in Access. You know you can control embedded Excel worksheets, but is there some way to call Excel functions from within Access?

12.3.2 Solution

Access users often ask how they can use Excel functions directly from Access. Using OLE Automation, you can actually request Excel to use its built-in functions to perform calculations and return a value back to your Access application. This requires starting Excel, however, and that can take time, so you wouldn't normally do this for a single calculation. But for a number of calculated values or a single calculation that would be too difficult or time-consuming in Access, it's worth tapping into the connections between Access and Excel.

There are many ways to use Automation to link Excel and Access. You can embed an Excel spreadsheet or chart object into an Access form and control the Excel objects programmatically, as in the example shown in the Solution in Recipe 12.6. You can also use OLE Automation from Access to create and manipulate Excel objects without using an embedded spreadsheet or chart. These methods are detailed in both the Access and Excel manuals. This solution uses the Excel application engine without creating any other specific Excel objects.

To test the OLE communication between Access and Excel, load frmTestExcel from 12-03.MDB and click the button on the form to start the test. The code attached to the button will start up Excel and run a series of tests, calling Excel to retrieve the results for a number of function calls. After all the tests, the sample form will look like Figure 12-5. You can run the tests either by writing directly to spreadsheet cells to test the multiple-value functions or by using arrays. The checkbox on the form lets you try both methods.

Figure 12-5. frmTestExcel after its function calls are completed

The sample form tests two different types of function calls you can make to Excel from Access: functions that accept simple parameters, and functions that require multiple values (ranges) as parameters.

The following steps describe how set up the example form:

12.3.3 Discussion

Excel obligingly exposes all of its internal functions to external callers via the Application object. The following sections describe the necessary steps to call Excel functions directly from Access.

No matter which Excel function you call, the return value will be a variant. Declare a variable as a variant if it will contain the return value from an Excel function. In the examples, the return values went directly to a text box, so you didn't need to select a data type.

12.3.3.1 Setting up communication with Excel

Before you can call any Excel function, you must start Excel and create an object variable in Access to link the two applications. You'll always use code like this to create this linkage:

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")

By linking with Excel's Application object, you can request Excel to evaluate any of its internal functions for you. Creating the object will take a few seconds, as Excel needs to be started. Calling CreateObject will start a new hidden instance of Excel, even if Excel is already running.

You have two other choices. If you know Excel is already running, you can use GetObject to retrieve a reference to an object within Excel or to the Excel Application object. The following code will retrieve a reference to the Application object if Excel is already running:

Set objExcel = GetObject(, "Excel.Application")

If you've set up a reference to Excel using the Tools References... menu item (this is necessary for this example to run), you should be able to use the following code to retrieve a reference to the Excel Application object:

Set objExcel = New Excel.Application

12.3.3.2 Calling simple Excel functions

Once you've created your Access object that refers to the Excel Application object, you can ask Excel to perform simple calculations for you. For example, to use the Excel Product function, use code like this:

Dim varProd As Variant
varProd = obj.Product(5, 6)

After this call, the variable varProd will contain the value 30.

For example, TestExcel, in frmTestExcel's module, uses the following code fragment to call four Excel functions: Proper, Substitute, Median, and Fact. Each of these functions requires one or more simple parameters and returns a single value. (The AddLine function calls just add the value returned by the function call to the text box on the sample form. These four functions are the first four in the output text box.) The relevant code fragment is:

' String functions
AddLine "Proper:", obj.Proper("this is a test")
AddLine "Substitute:", obj.Substitute("abcdeabcdeabcde", "a", "*")

' Simple math functions
AddLine "Median:", obj.Median(1, 2, 3, 4, 5)
AddLine "Fact:", obj.Fact(10)

Excel supplies many simple functions like these that Access doesn't have. Some of these functions (Proper, for example) are easy enough to replicate in VBA (the StrConv function will convert strings to proper case), but if you already have a connection to Excel, it makes sense to use Excel to retrieve these sorts of values rather than writing the code yourself.

To call analytical or statistical functions in Excel, use the same technique. With the reference to the Excel Application object, call any function that takes simple parameters and returns a single value. The next four examples on the sample form call the Kurt, Skew, VDB, and SYD functions:

' Analytical functions
AddLine "Kurt:", obj.Kurt(3, 4, 5, 2, 3, 4, 5, 6, 4, 7)
AddLine "Skew:", obj.Skew(3, 4, 5, 2, 3, 4, 5, 6, 4, 7)
AddLine "VDB:", obj.VDB(2400, 300, 10, 0, 0.875, 1.5)
AddLine "SYD:", obj.SYD(30000, 7500, 10, 10)

Sometimes you'll need to call Excel functions that require a variable number of values, or you'll want to use the data in a table as the input to an Excel function. In these cases, you have two choices: you can either call the Excel function using a spreadsheet range as the input, or you can pass a VBA array directly to the function, which will convert the array and treat it as a built-in range of values. In either case, you'll need a method of getting the Access data into the spreadsheet or into an array so you can use that data as input to the function.

12.3.3.3 Calling Excel functions using ranges

To copy a column of data from an Access table or query into an Excel spreadsheet column, call the acbCopyColumnToSheet function, found in the basExcel module in 12-03.MDB:

Public Function acbCopyColumnToSheet( _
 objSheet As Excel.Worksheet, strTable As String, _
 strField As String, intColumn As Integer)

   ' Copy a column from a table to a spreadsheet.
   ' Place the data from the given field (strField) in
   ' the given table/query (strField) in the specified
   ' column (intColumn) in the specified worksheet object
   ' (objSheet).
   ' Return the number of items in the column.
   
   Dim rst As DAO.Recordset
   Dim db As DAO.Database
   Dim intRows As Integer
   Dim varData As Variant
   
   Set db = CurrentDb( )
   Set rst = db.OpenRecordset(strTable)
   Do While Not rst.EOF
      intRows = intRows + 1
      objSheet.Cells(intRows, intColumn).Value = rst(strField).Value
      rst.MoveNext
   Loop
   rst.Close
   acbCopyColumnToSheet = intRows
End Function

Given a reference to an Excel sheet, a table or query name, a field name, and a column number for the Excel sheet, acbCopyColumnToSheet walks down all the rows of Access data, copying them to the Excel sheet. The function returns the number of rows that it copied over to Excel. For example, to copy the Unit Price field values from the tblProducts table to the first column of the open spreadsheet in Excel, use:

intCount = acbCopyColumnToSheet(objSheet, "tblProducts", "Unit Price", 1)

To keep it simple, this version of the acbCopyColumnToSheet function doesn't include error checking, but any code used in real applications should check for errors that might occur as you move data from Access to Excel.

Once you've copied the data to Excel, you can create an object that refers to that range of data as a single entity. Most Excel functions will accept a range as a parameter if they accept a group of values as input. For example, the Median function used previously accepts either a list of numbers or a range.

To create a range object in Access, use the Range method, passing a string that represents the range you want. The following example, used after the form copies the data from a table over to Excel, calculates the median of all the items in the column:

Dim objRange1 As Excel.Range

Set objRange1 = objSheet.Range("A1:A" & intCount)
AddLine "Median:", obj.Median(objRange1)

Some Excel functions require two or more ranges as input. For example, the SumX2PY2 function, which returns the sum of the squares of all the values in two columns (that is, x^2 + y^2), takes two ranges as its parameters. The following code fragment, also from the sample form, copies two columns from tblNumbers to the open sheet in Excel and then performs a number of calculations based on those columns:

' Copy two fields to columns.
intCount = acbCopyColumnToSheet(objSheet, "tblNumbers", "Number1", 1)
intCount = acbCopyColumnToSheet(objSheet, "tblNumbers", "Number2", 2)

' Create ranges.
Set objRange1 = objSheet.Range("A1:A" & intCount)
Set objRange2 = objSheet.Range("B1:B" & intCount)

' Print out calculations based on those ranges.
AddLine "SumX2PY2:", obj.SumX2PY2(objRange1, objRange2)
AddLine "SumSQ:", obj.SumSQ(objRange1)
AddLine "SumProduct:", obj.SumProduct(objRange1, objRange2)
AddLine "StDev:", obj.STDEV(objRange1)
AddLine "Forecast:", obj.ForeCast(5, objRange1, objRange2)
AddLine "Median:", obj.Median(objRange1)

12.3.3.4 Calling Excel functions using arrays

Rather than writing to a spreadsheet directly, you might find your work faster if you load a column of data into an array and send it to Excel that way. This avoids multiple Automation calls to Excel (each time you place a value into a cell in Excel, you're going through a lot of internal Automation code). The drawback, of course, is that you're loading all your data into memory. On the other hand, if you're working with so much data that it won't fit into memory, Automation will be too slow to be of much use, anyway!

To copy a column of data to an array, call the acbCopyColumnToArray function (from basExcel in 12-03.MDB), shown in the following code. Pass a variant variable (variants can hold entire arrays in VBA), a table name, and a field name to the function, and it will return the number of rows it placed into the array. This function walks through all the rows in your recordset, copying the values from the specified column into the array:

Public Function acbCopyColumnToArray( _
 varArray As Variant, strTable As String, strField As String)

   ' Copy the data from the given field (strField) of the
   ' given table/query (strTable) into a dynamic array (varArray)
   
   ' Return the number of rows.
   
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim intRows As Integer
   
   Set db = CurrentDb( )
   Set rst = db.OpenRecordset(strTable)
   rst.MoveLast
   ReDim varArray(1 To rst.RecordCount)
   rst.MoveFirst
   Do While Not rst.EOF
      intRows = intRows + 1
      varArray(intRows) = rst(strField).Value
      rst.MoveNext
   Loop
   rst.Close
   acbCopyColumnToArray = intRows
End Function

Once you've copied the data into arrays, you can call functions in Excel, passing those arrays as if they were ranges. Excel understands that it's receiving multiple values and returns the same results as the tests involving ranges:

' Copy two fields to columns.
Call acbCopyColumnToArray(varCol1, "tblNumbers", "Number1")
Call acbCopyColumnToArray(varCol2, "tblNumbers", "Number2")

' Print out calculations based on those ranges.
AddLine "SumX2PY2:", obj.SumX2PY2(varCol1, varCol2)
AddLine "SumSQ:", obj.SumSQ(varCol1)
AddLine "SumProduct:", obj.SumProduct(varCol1, varCol2)
AddLine "StDev:", obj.STDEV(varCol1)
AddLine "Forecast:", obj.ForeCast(5, varCol1, varCol2)
AddLine "Median:", obj.Median(varCol1)

This method is both simpler and faster than writing to a spreadsheet. However, if you're working with large volumes of data, you'll want to copy the data to a spreadsheet for Excel to process instead of copying it all into an array.

12.3.3.5 Closing Excel

Once you're done with your Access/Excel session, you must close the Excel application. If you don't, OLE will continue to start new instances of Excel every time you attempt to connect with Excel.Application (using CreateObject), eating up system resources each time.

To close Excel, use its Quit method:

obj.Quit

Finally, release any memory used by Access in maintaining the link between itself and Excel. The following code releases any memory that the reference to Excel might have been using:

Set obj = Nothing

12.3.4 Comments

Because it takes time to start Excel once you call the CreateObject function, build your applications so that all work with Excel is isolated to as few locations in your code as possible. Another alternative is to make your object variables global; then, you can have your application start Excel if it needs to and leave it open until it's done. Don't forget to close Excel, however, to avoid using up your system memory and resources.

When you're done with the Automation application, you'll need some way of closing down. As with the CreateObject function, each application reacts differently to your attempts to shut it down. You'll need to know how each application you use expects to be closed. Excel won't quit unless you explicitly order it to, using the Quit method. If you just set the object variable that refers to Excel.Application to the value Nothing without executing the Quit action, the hidden copy of Excel will continue running, chewing up memory and resources.

Excel exposes rich and varied inner workings via Automation, but taking advantage of those capabilities is nearly impossible without reference materials. This solution barely scratches the surface of what's available to you in Access from Excel. If you need to use the two products together, use the Object Browser in the Visual Basic Editor to explore the objects in the Excel object model. You can bring up the help topic for each object from within the Object Browser.

12.3.5 See Also

A good reference book for Excel programming is Writing Excel Macros by Steven Roman (O'Reilly). The Solution in Recipe 12.6 will give you a chance to explore a few of the more interesting corners of the Excel object model. For more information on sorting, using VBA, see Recipe 7.7 in Chapter 7.