We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

[SOLVED] XIRR Function Illegal Argument Exception [closed]

asked 2012-11-12 04:05:27 +0200

Ron gravatar image

updated 2012-11-12 15:51:39 +0200

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 00:26:35.533094

1 Answer

Sort by » oldest newest most voted

answered 2012-11-12 15:49:58 +0200

Ron gravatar image

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.

edit flag offensive delete link more


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

qubit gravatar imagequbit ( 2012-11-20 03:53:46 +0200 )edit

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?

Ron gravatar imageRon ( 2012-11-22 14:47:07 +0200 )edit

Hi @Ron -- 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. how to select an Answer

qubit gravatar imagequbit ( 2012-11-22 15:46:21 +0200 )edit

Question Tools

1 follower


Asked: 2012-11-12 04:05:27 +0200

Seen: 410 times

Last updated: Nov 12 '12