Hi all,I am trying to use XNPV from within VBA, but don't want to read its (range) aruments from an excel sheet. I am a complete beginner in vba, so would appreciate help on getting the code ( which is giving a run-time type mismatch error) listed below to run. I realize i should be type-casting the arguments of XNPV, but don't know how to do it.sub test() Dim cash_flow() as double dim payout_dates() as date count = application.count.range("cash_flow") redim cash_flow(count) redim payout_dates(count) for i = 0 to count - 1 cash_flow(i) = range("cash_flow1").offset(i,0).value payout_dates(i) = range("payout_dates").offset(i,0).value next i discount_rate = range("discount_rate").value ans = xnpv(discount_rate,cash_flow,payout_dates) End sub ' This whole procedure has to be run in a loop and the cash_flow and payout date arrays will be modified in each of these. ' i dont want to write the data back to an excel range and then read the XNPV arguments from it as it will slow down my code substantially. Can you please suggest how to proceed

I'm not saying I know the solution, but I would suggest you state which line of code is giving you the error: this should help understand the problem better.T

A few suggestions:- always use option explicit- I like using option base 1, gets you rid of the n-1 nonsenseTry the following:sub test() Dim cash_flow() as double, payout_dates() as date, n as long, discount as double, ans as doublen = range("cash_flow").countredim cash_flow(n) redim payout_dates(n) for i = 0 to n- 1 cash_flow(i) = range("cash_flow").cells(i,0).value payout_dates(i) = range("payout_dates").cells(i,0).value next i discount_rate = range("discount_rate").value ans = xnpv(discount_rate,cash_flow,payout_dates) End sub

First, while you may have already done this, it is worth noting that you need to establish a reference to the Analysis Toolpack for VBA, specifically atpvbaen.xls.Next, there no need to loop through the elements of the data arrays in the spreadsheet. Try the following code instead.Sub test()Dim Date_Array As RangeDim Cash_Flow_Array As RangeDim discount_rate As Double, ans As DoubleSet Date_Array = Worksheets("Sheet1").Range("The_Dates") 'The_Dates is a range defined in the spreadsheetSet Cash_Flow_Array = Worksheets("Sheet1").Range("The_Cash_Flows") The_Cash_Flows is a range defined in the spreadsheetdiscount_rate = Worksheets("Sheet1").Range("discount_rate").Value 'discount_rate is a range defined in the spreadsheetans = XNPV(discount_rate, Cash_Flow_Array, Date_Array)End SubFinally, it is not a good idea to use the same name in both a spreadsheet range and in a VBA module (I'm referring to the "discount_rate" variable).Hope this is useful

A few suggestions:- always use option explicit- I like using option base 1, gets you rid of the n-1 nonsenseTry the following:sub test() Dim cash_flow() as double, payout_dates() as date, n as long, discount as double, ans as doublen = range("cash_flow").countredim cash_flow(n) redim payout_dates(n) for i = 0 to n- 1 cash_flow(i) = range("cash_flow").cells(i,0).value payout_dates(i) = range("payout_dates").cells(i,0).value next i discount_rate = range("discount_rate").value ans = xnpv(discount_rate,cash_flow,payout_dates) End sub________Appreciate the suggestions. However, I still get the type-mismatch run-time error on the last line, the problem being that the second and third arguments of xnpv are required to be ranges and not arrays of double and date, respectively. Doesn't VBA afford the facility of type-casting these into ranges ?

David,thanks for your suggestions.As i said, I want to run the code fragment inside another loop, so the full code would look something like this:sub test() Dim cash_flow() as double dim payout_dates() as date dim ans() as doublefor j = 0 to n-1count = application.count.range("cash_flow") redim cash_flow(count) redim payout_dates(count) redim ans(count)for i = j to count - 1 cash_flow(i) = range("cash_flow1").offset(i,0).value payout_dates(i) = range("payout_dates").offset(i,0).value next i discount_rate = range("discount_rate").value ans(j) = xnpv(discount_rate,cash_flow,payout_dates) update(cash_flow1)' havent coded its functionality yet, but it will only affect the jth and subsequent elementsnext jEnd sub In case i use the Set Cash_Flow_Array as Range construct, i lose the flexibility of modifying the elements without writing the data back to the excel sheet ( through the update function). I want to avoid the overhead of referencing the excel sheet again after i have read in the data once.The best solution seemed to be to type-cash cash_flow and payout_dates in my code to the type range when i call the xnpv function. Is there some way of achieving it or am i totally off the track ?

I give a path on data sheet at a20, get an error. What am I doing wrong?Sub Savesheet()Sheets("Portfolio").Select Sheets("Portfolio").Copy ActiveWorkbook.SaveAs FileName:=Workbooks("Assets").Worksheets("data").Cells(20, "a").Value, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Sheets("Portfolio").SelectEnd Sub