Pass ParamArray to User Defined Function 
Author Message
 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)

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

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",
If rst.EOF Then
    ReturnsCount = 0
    Exit Sub
    ReturnsCount = rst.RecordCount
    ReturnsRows = ReturnsCount
End If

Returns = rst.GetRows(ReturnsRows)

'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
End Function

Sun, 17 Apr 2005 04:10:20 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Passing a user defined type to a function.

2. Passing User-defined types to public functions?

3. Help with passing user defined types as variables in functions - VB4 32bit

4. Help: Question about passing user-defined type to a function

5. Passing user defined types to functions/procedures

6. Passing User-defined type to a public function?

7. Passing Public user-defined types via Public Functions

8. User Control and User Defined Type Passing

9. Functions - User Defined Functions

10. User-defined error when the user doesn't define any errors

11. User Control - User-defined type not defined

12. Passing Array or a user defined data type as a parameter to a storedprocedure in Oracle


Powered by phpBB® Forum Software