Formula Length Limit in Excel

Nov 12, 2012 at 10:05 PM
Edited Nov 12, 2012 at 10:06 PM

Hello, Sir

I use NetOffice 1.2 (have not got a chance to update yet)

I know it should not be limited to 255

From the spec of excel 2003, it is 1024

but when I set in code like this

range.Formula = formuaStr;

When formulaStr is longer than 255, I get an exception

Name:TargetInvocationException
Message:Exception has been thrown by the target of an invocation.
Target:System.Object InvokeDispMethod(System.String, System.Reflection.BindingFlags, System.Object, System.Object[], Boolean[], Int32, System.String[])
Stack:   at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at LateBindingApi.Core.Invoker.PropertySet(COMObject comObject, String name, Object[] value)

Name:COMException
Message:Exception from HRESULT: 0x800A03EC

 

thanks

Coordinator
Nov 13, 2012 at 9:17 PM

255 is the well known limit for formulas, range adresses and so on regardless from NetOffice, Interop, VSTO or each other API trough the COM Object model in .NET
can i see the document what say the limit is 1024 ???

Sebastian
 

Nov 14, 2012 at 3:52 PM

thanks for your reply.  I check Excel 2003 specification @ http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

Nov 14, 2012 at 5:02 PM
Edited Nov 16, 2012 at 3:49 PM
Coordinator
Nov 14, 2012 at 10:30 PM

this looks good. thank you! (the replace trick is the best option i find)

Sebastian

Nov 15, 2012 at 3:11 PM

Thank you all very much.

I tried the following in C#, does not work.  Do you know how to replace in C#? thanks 

 

var tmpformula = "~~~"

;

rangeFunction.Formula = tmpformula;

rangeFunction.Replace(tmpformula, formula,

null, XlSearchOrder.xlByRows, null, null

);

Nov 15, 2012 at 3:20 PM

I use this rangeFunction.Replace(tmpformula, formula) to get replace work. However, if formula is longer than 255,  I still get the same exception as before. thanks

Nov 15, 2012 at 9:38 PM
Edited Nov 16, 2012 at 3:50 PM

The „trick“ is not to replace the hole value, but only max 255 characters!

 

Eg .

 

Tmpformula1 = “First_part_of_formular_max_252_charuntil_here ~~~”;

tmpformula 2 = “Second_part_of_formular_max_252_charuntil_here ~~~”;

tmpformula 3 = “Third_part_of_formular_max_252_charuntil_here~~~”;

 

 

rangeFunction.Formula = Tmpformula1;

rangeFunction.Replace(“~~~”, tmpformula2, null, XlSearchOrder.xlByRows, null, null);

rangeFunction.Replace(“~~~”, tmpformula3, null, XlSearchOrder.xlByRows, null, null);

 

 

 


From: toosensitive

I use this rangeFunction.Replace(tmpformula, formula) to get replace work. However, if formula is longer than 255, I still get the same exception as before. thanks

Nov 16, 2012 at 6:10 PM
Edited Nov 16, 2012 at 6:12 PM


Thanks, I tried your approach, still get the same exception.
I guess that's b/c I cut formula in short and make it invalid formula.
Then I tried this, it seems this works till 323 chars, but I get the same exception if the length is longer

 

                    var tt = XLApp.ErrorCheckingOptions;
                    XLApp.ErrorCheckingOptions.InconsistentFormula = false;
                    XLApp.ErrorCheckingOptions.EvaluateToError = false;
                    XLApp.ErrorCheckingOptions.UnlockedFormulaCells = false;
                    XLApp.DisplayFormulaAutoComplete = false;

                    rangeFunction.Formula = formula;

Nov 20, 2012 at 7:43 PM

Thanks all for your help.

I find using & will solve the issue.  say MyUDF(param1,...)

If Param1 is longer than 255, cut it into trunks (255 or less), then using & to cancatenate them

eg param1 = subpart1 & subpart2, etc