Excel Function

[Forrest]

My goal is to have a Access2002 form/report show the IRR
(internal rate of return) of a series of distributions
stored in an Access table. The table looks like:
Name Date Amount
Alloy Corp. 4/4/2000 ($5.00)
Alloy Corp. 12/2/2002 $2.00
Alloy Corp. 6/11/2003 $3.00
Alloy Corp. 5/3/2002 $4.00
Alloy Corp. 3/31/2003 $5.00

The following code on my form produces a result
referencing the current record:

Private Sub xlAverage_Click()
Dim objExcel As Excel.Application
Dim dbs As Database
Dim rst As Recordset
Dim N As Long
Dim recno As Long
Set objExcel = CreateObject("Excel.Application")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("AlloyIRRForrest")
MsgBox objExcel.Application.WorksheetFunction.Average
([Amount])
objExcel.Quit
Set objExcel = Nothing

End Sub

My challenge; 1)I need to get the average of all records,
and 2)I need to reference the IRR function which will
involve two fields.

Thanks for any suggestions.

Forrest

 

[Joe Fallon]

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

Originally posted by Max Dunn