Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Create a new form containing a single text box (named
   txtResults on the sample form) and a command
   button to run the Excel tests (as in Figure 12-5).

2. Import the module basExcel from
   

   12-03.MDB . This module contains a function that
   copies data from a column in Access to a spreadsheet column in Excel.
   The module also includes a function that copies data from a column in
   Access to an array, which OLE Automation can use in place of a range.

3. Enter the following code into the form's module
   (click on the Build button on the toolbar or choose View
   Code):

   Private Sub AddLine(strLabel As String, varValue As Variant)
   Me.txtResults = Me.txtResults & vbCrLf & _
   " " & Left(strLabel & Space(20), 20) & varValue
   DoEvents
   End Sub 
   Private Function TestExcel( )
   Dim obj As Excel.Application
   Dim intCount As Integer
   Dim blnUseArrays As Boolean
   Me.txtResults = Null
   blnUseArrays = Nz(Me.chkUseArrays)
   DoEvents
   AddLine "Starting Excel:", "Please wait..."
   ' If you know Excel is open, you could use GetObject( )
   Set obj = CreateObject("Excel.Application")
   ' Clear out the results text box.
   Me.txtResults = Null
   DoEvents
   ' 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)
   ' 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)
   If blnUseArrays Then
   ' Using arrays
   Dim varCol1 As Variant
   Dim varCol2 As Variant
   ' 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)
   Else
   ' Using ranges
   Dim objBook As Workbook
   Dim objSheet As Worksheet
   Dim objRange1 As Range
   Dim objRange2 As Range
   ' Create the workbook.
   Set objBook = obj.Workbooks.Add
   Set objSheet = objBook.WorkSheets(1)
   ' 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)
   ' Convince Excel that it needn't save that
   ' workbook you created.
   obj.ActiveWorkbook.Saved = True
   Set objRange1 = Nothing
   Set objRange2 = Nothing
   Set objSheet = Nothing
   End If
   ExitHere:
   ' Quit and clean up.
   obj.Quit
   Set obj = Nothing
   End Function

4. In the properties sheet for the
   command button, enter the value:

   =TestExcel( )

   in the OnClick event property.

5. With a
   module open in design mode, choose the Tools References...
   menu item. Choose Microsoft Excel 11.0 Object Library from the list
   of choices (this item will be on the list if you installed Excel
   correctlyselect the version that you have installed, which may
   be something besides Excel 11.0). This provides your VBA code with
   information about the Excel object library, properties, methods, and
   constants.

6. Open the form in run mode and click the command button. This will
   call the TestExcel function and fill the text box with the results.

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.