NPV Function not working

[JOD59]

I have been trying to use the NPV Function (net present value) in a form with no luck. Ideally I wanted to use a text box to calculate the value based on input I.e.
NPV(Discountrate,year1,year2,year3...etc)but judging from what MS had on their site I don’t know if it will be possible. I tried their example and keep getting an error. If anyone has any suggestions please post. Thanks

Here is what MS had on their site

SUMMARY
The NPV (Net Present Value) function is only available in Visual Basic for Applications. However, you can write a custom function so that the NPV function is accessible from Access objects such as forms, reports, and queries. This article demonstrates how to do this.
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.

 

[MichaelRed]

Sorry, I don't see the "problem". It works as advertised.

MichaelRed

 

[VBslammer]

If you're trying to set the controlsource with textbox input from the user, which would look something like this:

=myNPV([txtReturn],[txtStartup],[txtYear1],[txtYear2],[txtYear3],[txtYear4])

you'll need to add an error handler to the function so errors will be ignored when the textboxes are null:

Function myNpv(RetRate As Double, ParamArray arValues() As Variant) As Currency
On Error GoTo ErrHandler

   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())
   
ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

I also added a return type of Currency instead of Variant. I didn't have any problems getting it to work either.

VBSlammer

Unemployed in Houston, Texas

 

[JOD59]

I must be doing something wrong, I keep getting this error "expected variable or procedure not project" I'm new to working with VB. I'm running access 2000, and I’ve read about sandbox issues and functions not being allowed. So I’ve done the fix that MS has on their web sit, still no luck.

 

[PHV]

Have by chance the module and the function the same name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JOD59]

Found the Problem!!! Thanks for your vbSlammer

ACC2000: Same Named Module and Procedure Causes Errors
Article ID : 210098
Last Review : July 14, 2004
Revision : 1.0
This article was previously published under Q210098
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).


On this Page
SYMPTOMS
CAUSE
RESOLUTION
MORE INFORMATION

SYMPTOMS
When you run a procedure from an object's events, you may receive the following error message:
The expression [event name] you entered as the event property setting produced the following error: The expression you entered has a function name that Microsoft Access can't find.
If you use the RunCode action in a macro to call a function, you may receive the following error message:

The expression you entered has a function name that Microsoft Access can't find.
If you try to run a procedure from the Immediate window, you may receive the following error message:

Compile Error:
Expected variable or procedure, not module.
NOTE: All the modules in the database or project will appear to compile successfully.
CAUSE
A user-defined function or a subroutine have the same name as a module. These errors occur even if the procedure is not contained in the module with the same name.
RESOLUTION
Change the name of the module so that it is different from the name of any procedure that you have in the database or project.
MORE INFORMATION
Steps to Reproduce Behavior
1. Create a new Microsoft Access database, and then create a module.
2. Type the following procedure:Function Test()
MsgBox "Does this work correctly?"
End Function


3. Save the module as Test.
4. Type the following line in the Immediate window, and then press ENTER:Test




--------------------------------------------------------------------------------