locked
activecell.formula is there a max length that can be passed? RRS feed

  • Question

  • I have been successfully using a macro to create a subtotal formula of necessary cells.  e.g. =A1 + A2 + A3 + A4, etc.

    This macro has worked consistently in the past.  However NOW, I seem to have hit a (invisible) wall.   Below is the specific code line that is causing the macro to crash with a 1004 error message.    This macro works for adding several hundred cells togetther, but I need to know if there is a limit, because now when trying to add over 380 cells together it is crashing the macro.

    According to what info I can find, strings can be massive, much longer that the approx 3400 character formula my macro creates.

          NOTE:     subTformula is String, i is an integer,  and  lastCol is an integer.  subTformula is constructed by concatening cell referenences togetrher and the formula generated looks just fine.  It only when we try to get that long subTfromula string into the activecell that the macro crashed.   Like I said works for at least 200+ cell reference formula, but for 385 cell references it crashes.

                        ActiveCell.offset(i, lastCol).Formula = subTformula

    thanks in advance

    Tuesday, March 27, 2012 7:33 PM

Answers

  • HI Hans-

    The character count for that problematic created formula is only about 3300 characters.   I believe everything is well within the documented MS limits.  Thanks.

    I decided to re-write the code so it will produce a sum function instead of adding individual cells.   I have to say it does still bother me that there seems to be some glass ceiling for constructed formula length that is undocumented, though I don't have the time or patience right now to be the one who discovers and documents.

    • Marked as answer by LALA Florida Monday, April 9, 2012 6:32 PM
    Monday, April 9, 2012 6:32 PM

All replies

  • I have been successfully using a macro which takes data out of an array and as appropriate creates a subtotal formula of the necessary cells.  e.g. =A1 + A2 + A3 + A4, etc.

    This macro has worked consistently in the past.  However NOW, I seem to have hit a (invisible) wall.   Below is the specific code line that is causing the macro to crash with a 1004 error message.    This macor works for adding several hundred cells togetther, but I need to know if there is a limit, becasue now it is trying to add over 300 cells together and it is crashing the macro.

    According to what info I can find, strings can be massive, much longer that the approx 3400 character formula my macro creates.

          NOTE:     subTformula is String, i is an integer,  and  lastCol is an integer

                        ActiveCell.offset(i, lastCol).Formula = subTformula

    thanks

    • Moved by Youen Zen Thursday, March 29, 2012 2:39 AM Not VB issue (From:Visual Basic Language)
    • Merged by danishani Wednesday, April 4, 2012 12:46 AM duplicate
    Tuesday, March 27, 2012 6:34 PM
  • Hi LaLa

    this sounds like a question for the VBA Forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

    but you can look through some of these for that particular error

    http://www.google.com/search?q=excel+error+1004&rlz=1I7DKUS_enUS278&ie=UTF-8&oe=UTF-8&sourceid=ie7


    “This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.”


    • Edited by jwavila Tuesday, March 27, 2012 6:59 PM
    Tuesday, March 27, 2012 6:56 PM
  • Tuesday, March 27, 2012 7:36 PM
  • Do you really need to sum 385 individual cells? Can't you combine them into ranges?

    Regards, Hans Vogelaar

    Tuesday, March 27, 2012 8:21 PM
  • Hi Hans-

    Obviously if I cannot find the answer as to why VBA is generating the error then I will have to totally change my procedure.  The goal is understand why I am getting this error, so that I know if there is an easier fix than totally revamping a procedure that has been working just fine until now.

    thanks

    Tuesday, March 27, 2012 8:28 PM
  • According to the Excel specifications and limits, the maximum length of a formula is 8192 characters.

    I managed to use VBA to create formulas of up to 8192 characters without problems. 8193 caused an error, as was to be expected.


    Regards, Hans Vogelaar

    Tuesday, March 27, 2012 9:12 PM
  • HI Hans-

    The character count for that problematic created formula is only about 3300 characters.   I believe everything is well within the documented MS limits.  Thanks.

    I decided to re-write the code so it will produce a sum function instead of adding individual cells.   I have to say it does still bother me that there seems to be some glass ceiling for constructed formula length that is undocumented, though I don't have the time or patience right now to be the one who discovers and documents.

    • Marked as answer by LALA Florida Monday, April 9, 2012 6:32 PM
    Monday, April 9, 2012 6:32 PM