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

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
 

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

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

Sebastian

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;

null, XlSearchOrder.xlByRows, null, null

);

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

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;

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