[SOLVED] XIRR Function Illegal Argument Exception

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:
image description

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.

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.

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

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?

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.