Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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 code 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 code 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: Array(values.DataArray, dates.DataArray) 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. XIRR takes a 1Xn array of values and a 1Xn array of the same number of dates.

click to hide/show revision 2
format the code snippets & include the image correctly

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 code parameter construction that works: _ works:

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

Here is the code parameter construction that fails: _ fails:

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

Here are the data and their structure as viewed in the basic editor: Array(values.DataArray, dates.DataArray) 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. 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.

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.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.

[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.