-1

I am developing an excel add-in with the help of Excel-DNA and facing a problem with the formula having more than 255 characters. I am pulling data from API of different tickers, all is good until Array Resizer is called. In the DoResize() function exception is thrown at the following line of code.

//Set the formula into the whole target
formulaR1C1 = (string) XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);

And the exception description is as follows:

Exception Type: System.InvalidCastException
Exception Message: Unable to cast object of type 'ExcelDna.Integration.ExcelError' to type 'System.String'.

I am using Excel 2013 32-bit with Excel-DNA 0.30.

Govert
  • 16,387
  • 4
  • 60
  • 70
Nadeem Akhtar
  • 11
  • 3
  • See the discussion on the Excel-DNA Google group: https://groups.google.com/forum/#!topic/exceldna/URF0fiIZnjg – Govert Mar 19 '14 at 10:11

1 Answers1

0

The error might be due to a limitation of the Excel C API call that is used in the implementation of the Excel-DNA ArrayResizer example. Historically, strings in the Excel C API were limited to 255 characters. In Excel 2007 this limitation was removed (with the introduction of Unicode strings up to 64000 characters.) However, many of the C API function have not been updated, and still only support 255 character strings. The xlfFormulaConvert function seems to be one such case.

If so, there not much to do other than convert the whole ArrayResizer helper to use the COM Automation interfaces instead of the C API.

You might be able to use Excel defined names to shorten your formula, by defining names for subformulae, or you might refer to other cells in the sheet which compute sub-parts.

Update: The same limitation is present when setting Range.FormulaArray using the COM interface (http://support.microsoft.com/kb/213181). See http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ for an extensive discussion and workarounds.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • Workaround given at http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ is not working. – Nadeem Akhtar Mar 21 '14 at 10:50