ACC2000: Parameter Arrays Do Not Work with NPV Function (268159)

MORE INFORMATION

Because the NPV function allows only arrays of type Double, you cannot implement it with a parameter array because parameter arrays must always be a variant data type. The solution is to first pass the parameters to the custom function, and then to feed them into a second array that is a double data type. The following steps demonstrate an example of this:

1. In a new Access database, create a new Visual Basic for Applications module.
2. In the module, type or paste the following function:

   Function myNpv(RetRate As Double, ParamArray arValues() As Variant)
       Dim intI As Integer
       
       ' Use UBound function to determine upper limit
       ' of array and set arLocValues to same size.
       ReDim arLocValues(UBound(arValues)) As Double
        
       ' Cycle through and put arValues into
       ' the Double Type arLocValues
       For intI = 0 To UBound(arValues())
           arLocValues(intI) = arValues(intI)
       Next intI
       
       'Find the NPV using the required Double type array.
       myNpv = NPV(RetRate, arLocValues())
   End Function
   						

3. Save the module, and then close the Visual Basic Editor.
4. Create a new form not based on any table or query named Test.
5. Add a text box named Text0 to the Test form.
6. In the property sheet for the text box, click the Data tab, and then set the ControlSource property as follows:

   =myNPV(0.0625,-70000,22000,25000,28000,31000)

   In this example, 0.0625 is the fixed internal rate of return, -70000 is the business start-up cost, and the rest are positive cash flows reflecting income for four successive years.
   
   NOTE: You can enter whatever number of cash flows that you want; they will be fed into the array as needed.
7. In the property sheet, click the Format tab, and then set the Format property to Currency.
8. Close the property sheet, and then view the form in Form view. Note the Net Present Value of $19,312.57.