none
Exception:Unable to set the FormulaArray property of the Range class RRS feed

  • Question

  • The following codes raises an exception

    Range rng = ((Worksheet)applicationObject.ActiveWorkbook.Sheets["收支记录"]).get_Range("I2");
    
    rng.FormulaArray = "=SUM(IF(INDIRECT(\"收支记录!R2C7:R\"&SUM(ROW())&\"C7\",FALSE)=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE),INDIRECT(\"收支记录!R2C3:R\"&SUM(ROW())&\"C3\",FALSE),0),-IF((转账记录!R2C3:R65536C3=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE))*(转账记录!R2C1:R65536C1<=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C1\",FALSE)),转账记录!R2C2:R65536C2,0),IF((转账记录!R2C4:R65536C4=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE))*(转账记录!R2C1:R65536C1<=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C1\",FALSE)),转账记录!R2C2:R65536C2,0),IF(账户信息!R2C3:R65536C3=INDIRECT(\"收支记录!R\"&SUM(ROW())&\"C7\",FALSE),账户信息!R2C4:R65536C4,0))";

    But if i copy and paste the above formulas to the formula bar of Excel 2010 and press Ctrl+Shift+Enter, it work perfectly!

    I used Range.FormulaArray elsewhere in my program and it worked fine. Maybe someone can point out the problem.

    Friday, November 29, 2013 8:45 AM

Answers

  • Hi Sirius,

    Please note that FormulaArray property has a character limit of 255. How many characters does your formula contains?

    Did you try to set simple values to be sure that you can set the property at all? For example:

    Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"
    

    • Marked as answer by Sirius Lee Friday, December 6, 2013 9:04 AM
    Wednesday, December 4, 2013 12:29 PM

All replies

  • Hello Sirius,

    Which line of code fires an exception?

    Anyway, please try to use a verbatim string instead (use the "@" character before the string). It will allow you to remove all additional escape characters from the very long string.

    Friday, November 29, 2013 10:45 AM
  • Sorry for late reply.

    The second line fires the exception.

    I have reviewed the very long formula.I am sure no escape characters in it.The problem still exists.

    Anyway, thank you for your suggestion.


    Wednesday, December 4, 2013 2:18 AM
  • Hi Sirius,

    Please note that FormulaArray property has a character limit of 255. How many characters does your formula contains?

    Did you try to set simple values to be sure that you can set the property at all? For example:

    Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"
    

    • Marked as answer by Sirius Lee Friday, December 6, 2013 9:04 AM
    Wednesday, December 4, 2013 12:29 PM
  • Thanks,Eugene.

    I forgot the limit.

    In addition,did you know how to apply such long formula in C#? This formula make many things simple. I really not want give up it.

    Friday, December 6, 2013 9:16 AM
  • Hello Sirius,

    You can try to break the formula string into separate cells and then use these cells in calculating the final result.

    Friday, December 6, 2013 9:21 AM
  • Despite somewhat unsatisfactory, your proposal effectively solve the problem.

    Thank you, Eugene.

    Wednesday, December 18, 2013 4:56 AM
  • Good news, Sirius! :)

    Thank you for letting me know and good luck with your project !

    Wednesday, December 18, 2013 2:06 PM