Access Cookbook - Ken Getz [259]
' 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.
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)
TIP
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,