[SOLVED] XIRR Function Illegal Argument Exception

[Ron1]

I’m trying unsuccessfully to construct my own parameters to the XIRR() function.
When I use the DataArray property of a ScCellRange object it works.
When I manually constuct the same data (well almost) it gives an illegal argument exception.
Here is the parameter construction that works:

• dim dates as object: dim values as object: dim parms() as object
• dates = sheet.getCellRangeByPosition(0, startRownum, 0, activeRownum)
• values = sheet.getCellRangeByPosition(8, startRownum, 8, activeRownum)
• parms = Array(values.dataArray, dates.dataArray)

Here is the parameter construction that fails:

• dim valdata(activeRownum - startRownum) as variant
• dim datdata(activeRownum - startRownum) as variant
• dim parmsx() as object
• valdata = array(array(-18141#), array(20965.99484#))
• datdata = array(array(41026#), array(41213#))
• parmsx = Array(valdata, datdata)

Here are the data and their structure as viewed in the basic editor:

The data appear identical except for some differences in types.
I have been unable to exactly duplicate the types and don’t know if that is causing the problem.

I have been completely unsuccessful attempting to actually modify the DataArray property of the cell range object even though xray does not say it is read-only. It gives a run-time exception.

XIRR takes a 1Xn array of values and a 1Xn array of the same number of dates. Here are the respective calls to the XIRR function:

• dim result
• result = fA.callFunction(“XIRR”, parms)
• result = fA.callFunction(“XIRR”, parmsx)

The call with parmsx gives the exception.

[Ron1]

Found it! It was that parmsx(0)(0)(0) was an Integer and I didn’t catch it. Even though declared as a Double, its value was in the range of an integer and, so, was converted as an Integer. parmsx(1)(0)(0) was not in integer range, though, and was converted as a Double. Changing -18141 to -18141.0001 fixed it.

[qubit1]

@Ron1 – Please mark your question as answered (I’d do it, but don’t have enough karma :P). Thanks!

[Ron1]

Glad to, but don’t know how - find no answer button - edited title to say SOLVED, but not correct, I guess. How do I mark it as answered?

[qubit1]

Hi @Ron1 – There’s a little checkmark next to each answer on a question, like in the picture below. Selecting the best answer to your question will mark it as answered.