Problem using XNPV from VBA
Hello, I have this (to me) strange situation:
Dim aRangeValues() As Double, aRangeDates() As Date The two arrays are set to the foll. values (dates are in dd/mm/yyyy): aRangeValues(1) | aRangeValues(2) | aRangeValues(3) | aRangeValues(4) -1721.9482672 | 194.6875 | 194.6875 | 2194.6875 aRangeDates(1) | aRangeDates(2) | aRangeDates(3) | aRangeDates(4) 27/09/2002 | 30/09/2002 | 30/09/2003 | 30/09/2004 If I try to calculate the NPV (using XNPV - due to different intervals): Val = Application.Run("XNPV", 0.05, aRangeValues, aRangeDates) I get the #VALUE! error! Just to check the situation, I tried to calculate: Val = Application.Run("XIRR", aRangeValues, aRangeDates) and I get a correct 0.262366098165512 I tried to use the XNPV function in a cell, and it works! Any idea? Thanks, Dario |
Problem using XNPV from VBA
Sub Tester5()
Dim aRangeValues As Variant, bRangeTemp As Variant Dim aRangeDate() As Long aRangeValues = Array(-1721.9482672, 194.6875, 194.6875, 2194.6875) aRangeTemp = Array("09/27/2002", "09/30/2002", "09/30/2003", "09/30/2004") ReDim aRangeDates(LBound(aRangeTemp) To UBound(aRangeTemp)) For i = LBound(aRangeTemp) To UBound(aRangeTemp) aRangeDates(i) = CLng(CDate(aRangeTemp(i))) 'Debug.Print aRangeDates(i) ' <== use to check what is in date array Next dblRate = 0.05 res = Application.Run("ATPVBAEN.XLA!XNPV", 0.05, aRangeValues, aRangeDates) Debug.Print res End Sub It appears the second argument needs to be an array of longs rather than an array of dates - got an error when I passed it dates. Also, Val is a vba function. I would use a different variable than Val. I put your date in US format - VBA is US centric. Not sure how you are loading your array of dates, but if using strings at all and using implicit conversion I would use US formatted strings. If using Cdate, it is supposed to pay attention to regional settings, but I would check what is in the array to be sure. -- Regards, Tom Ogilvy Dario wrote in message m... Hello, I have this (to me) strange situation: Dim aRangeValues() As Double, aRangeDates() As Date The two arrays are set to the foll. values (dates are in dd/mm/yyyy): aRangeValues(1) | aRangeValues(2) | aRangeValues(3) | aRangeValues(4) -1721.9482672 | 194.6875 | 194.6875 | 2194.6875 aRangeDates(1) | aRangeDates(2) | aRangeDates(3) | aRangeDates(4) 27/09/2002 | 30/09/2002 | 30/09/2003 | 30/09/2004 If I try to calculate the NPV (using XNPV - due to different intervals): Val = Application.Run("XNPV", 0.05, aRangeValues, aRangeDates) I get the #VALUE! error! Just to check the situation, I tried to calculate: Val = Application.Run("XIRR", aRangeValues, aRangeDates) and I get a correct 0.262366098165512 I tried to use the XNPV function in a cell, and it works! Any idea? Thanks, Dario |
All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com