office 365 fixed 255 limit?

[daveyc18]

i recorded a macro with a long-*** formula that's over 1000 characters and it seemed to work when I ran the macro....i guess office 365 fixed the 255 character limit?

 

[Joe4]

I wasn't aware of that, but I never have formulas that long. They can be too unwieldy to maintain.
As long as you are already using VBA, you might want to consider converting that formula to a User Defined Function (UDF) in VBA.
Those are usually much easier to maintain, because you have them formatted in step-like code with indentations and separate lines, and you can add comments to it also.

 

[Anthony47]

Wasn't that a limit for vba Array formulas?

 

[daveyc18]

Wasn't that a limit for vba Array formulas?

no even for standard formulas . there was a limit. ..pretty sure it's 255

 

[Anthony47]

Humm...

    Range("I12").Select
    Selection.FormulaArray = _
"=SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])"

This formula is long 273 characters; if I try the code it will fail with Run time errpr 1004
But if we remove the last "HistoricalMaPerProva!" (this is a sheet name used for this testing), it will become 252 characters and the formula will be set in I12

    Range("I12").Select
    Selection.FormulaArray = _
"=SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*R[331]C[2]:R[336]C[2])"

Tested in Excel 365

In addition, help for FormulaArray states:

Remarks
The FormulaArray property also has a character limit of 255

Range.FormulaArray property (Excel)

Bye

 

[daveyc18]

there's definitely a limit on how long a standard formulas ..im talking r1c1 ..not formula array

 

[daveyc18]

this is how long my formula is:

=IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)<>0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)<>0,F7<>E7),"Price Adjustment / Backdate / Truncation",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)=0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)<>0,F7<>E7),"Price Adjustment and Truncation",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)<>0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)=0,F7<>E7),"Price Adjustment and Backdate",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)=0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)=0,F7<>E7),"Price Adjustment",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)<>0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)<>0,F7=E7),"Backdate and Truncation",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)<>0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)=0,F7=E7),"Backdate",IF(AND(SUMIFS('STR 100 Filtered'!K:K,'STR 100 Filtered'!Q:Q,AE7)=0,IFERROR(VLOOKUP(AE7,'WTR Truncation'!J:K,2,0),0)<>0,F7=E7),"Truncation","IGNORE; PROBABLY ON STR22, BUT BACKDATE ENDS UP BEING FLAT")))))))

 

[daveyc18]

Humm...

    Range("I12").Select
    Selection.FormulaArray = _
"=SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])"

This formula is long 273 characters; if I try the code it will fail with Run time errpr 1004
But if we remove the last "HistoricalMaPerProva!" (this is a sheet name used for this testing), it will become 252 characters and the formula will be set in I12

    Range("I12").Select
    Selection.FormulaArray = _
"=SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*HistoricalMaPerProva!R[331]C[2]:R[336]C[2])+SUM(HistoricalMaPerProva!R[331]C[1]:R[336]C[1]*R[331]C[2]:R[336]C[2])"

Tested in Excel 365

In addition, help for FormulaArray states:

Range.FormulaArray property (Excel)

Bye

also found this link

How to overcome the max length limit of a formula in Excel? a bug of excel?

There are at least two forms of formula in Excel. One is used for cells, and the other is used for data validation. The max length limit for the formula to do data validation is about only 210 char...

stackoverflow.com

 

[daveyc18]

the error I was previously was. PC using Excel 2010 and hence I expected the same in-office 365 ...perhaps it's no longer issue for Verizon's after 2010

 

[RoryA]

The max length of a formula is over 8000 characters as I recall but there was a bug in the macro recorder (not sure if it's still there) where very long formulas that would need splitting over multiple lines of code didn't get recorded properly - the recorder actually overwrote parts of the formula string with the necessary line continuation and concatenation symbols.