XNPV in VBA

[David]

I'm using Excel 2007 and I'm trying to calculate XNPV for a certain
array within VBA. Within Excel, the XNPV function asks for 3
arguments: XNPV(rate,value,dates), but within VBA, it is only asking
for 2 arguments. When I looked up XNPV within the Excel VBA Help, it
says the function calls for 2 arguments, but gives an example with 3.
When trying to use three arguments I get a compile error with this
message: "Wrong number of arguments or invalid property assignment" I
know it is not an invalid property assignment because I am using the
XIRR function (which only requires 2 arguments) with the same arrays.
Anyone have a solution? Here is code:

Range("H50") = WorksheetFunction.Xirr(NetCFAC, DateSet()) -- THIS
WORKED
Range("D49") = WorksheetFunction.Xnpv(0.05, NetCFAC, DateSet()) --
THIS DOES NOT WORK

Thanks

 

[Peter T]

I see what you are saying but not sure why, looks like a bug.

In the mean time maybe you can use something like this
x = Application.Evaluate("=XNPV(0.1,B1:B5,C1:C5)")

this should also work in earlier versions, as bug or not XNPV was only
introduced as a VBA workksheet function in Excel 2007.

Regards,
Peter T

 

[David]

Good suggestion, unfortunately I'm trying to calculate the XNPV with
arrays built within VBA. When I return this to Excel, it's looking
for Ranges with my VBA names and coming up with a #NAME? error. I
guess I will have to fudge something like this though since it seems
like an unfixable bug.

Thanks for you help