Pass ParamArray to User Defined Function

Hi all,

I am trying to calculate the Future Value of $1.00 after

applying a series of compound interest rates (this can be

done in Excel with FVSHCEDULE formula but there is no

corresponding function in Access).

My Function "CalcFV" works fine when I input rates in

immediate window:

?CalcFV (.05,.1,.08,-.02)

1.05

1.155

1.2474

1.222452

But when I try to pass a recordset from a 1 column query

using the ParamArray declaration in my sub "GetReturns()"

I get a type mismatch error as it tries to evaluate the FV

Calculation.

I probably not passing the array called "Returns" to my

Function CalcFV correctly. any suggestions? Thanks for

your help. Here is my coding:

______________________________________________________

Sub GetReturns()

Dim db As Database

Dim rst As Recordset

Dim Returns As Variant

Dim Counter As Integer

Dim FirstReturn As Double

Set db = CurrentDb

Set rst = db.OpenRecordset("qryStyle Returns Growth",

dbOpenSnapshot)

If rst.EOF Then

rst.Close

ReturnsCount = 0

Exit Sub

Else

rst.MoveLast

ReturnsCount = rst.RecordCount

ReturnsRows = ReturnsCount

rst.MoveFirst

End If

Returns = rst.GetRows(ReturnsRows)

rst.MoveFirst

'this returns correct number of records in query

Rows = UBound(Returns, 2)

FirstReturn = rst![PeriodicReturn]

Call CalcFV(FirstReturn, Returns)

End Sub

Function CalcFV(ByRef FirstReturn, ParamArray OtherReturns

() As Variant)

Dim StartValue, FV As Double

Dim Counter As Integer

StartValue = 1#

'to calculate first FValue of Starting $1.00

FV = (StartValue * FirstReturn) + StartValue

Debug.Print FV

For Counter = 0 To UBound(OtherReturns)

'to calculate subsequent Future Values produces a type

'mismatch using GetReturns sub where I define 'Returns'

'as array from getrows method...

FV = (FV * OtherReturns(Counter)) + FV

Debug.Print FV

Next

End Function