none
Setting a long Array formula RRS feed

  • Question

  • Hello,

    I want to set a long ArrayFormula (length > 300) in C# with the help of the Excel Interop.

    I found Years ago a workaround:

    	internal static void SendKeys(string keys)
            {
                _oExcelApp.SendKeys(keys, true);
                Application.DoEvents();
            }
    
    	internal static void AddFormulaArrayToSpreadsheet(Excel.Range er, string formula)
            {
                dynamic r1c1 = ConvertFormulaToR1C1(formula);
                if (r1c1 is string)
                {
                    er.FormulaArray = r1c1;
                }
                else
                {
                    AddFormulaToSpreadsheet(er, formula);
                    ScreenUpdating = true;
                    ScreenUpdating = false;
                    er.Worksheet.Activate();
                    er.Select();
                    SendKeys("{F2}^+{ENTER}");
                }
            }

    Before the call to  AddFormulaArrayToSpreadsheet screenupdating is set to false event are disabled and calculation is set to manual.

    It works when I testet it years ago, but now it fails.

    The selected range has no formulas at all.

    Does somebody see the failure ?

    with best regards

      Hendrik Schmieder

    Monday, July 2, 2018 2:43 PM

Answers

  • Hello h_schmieder,

    If you have an array formula contains more than 255 characters, you could set the formula to the range value directly and then as your code design, try to simulate the operation to force it as an array formula.

    However, you should not Send F2 and Shift+Ctrl+Enter keys together. Send F2 first and then Send Shift+Ctrl+Enter.

    The simple code looks like.

     if (formula.Length > 255) {
                    er.Value = formula;
                    SendKeys("{F2}");
                    SendKeys("^+{ENTER}");
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by h_schmieder Friday, July 6, 2018 8:48 AM
    Wednesday, July 4, 2018 7:48 AM
  • Thanks Terry,

    that does helped me a lot.

    I now use this code for the bigger case

    // er is Microsoft.Office.Interop.Excel.Range
    
    // it is sufficient to set the formula on the very first cell
    er.Cells[1, 1].Value = formula;
    
    er.Worksheet.Activate();
    er.Select();
    
    // Important : ScreenUpdating must be true before sending F2
    ScreenUpdating = true;
    
    SendKeys("{F2}");
    ScreenUpdating = false;
    SendKeys("^+{ENTER}");
    
    This is also works if er is a whole column or a whole row


    • Marked as answer by h_schmieder Friday, July 6, 2018 8:48 AM
    • Edited by h_schmieder Friday, July 6, 2018 8:48 AM wrong time case
    Friday, July 6, 2018 8:48 AM

All replies

  • Hello h_schmieder,

    What array formula do you want to set? What's your source data?

    What did you do in ConverFormulaToR1C1 and AddFormulaToSpreadSheet?

    I would suggest you share your detail code so we could reproduce your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 3, 2018 6:26 AM
  • The array formula I want to set is

    =PALO.SUBSET("Umlaut/MPRO_FINANCE","Main Account",1,,PALO.HFILTER("All Accounts",0,False,2),PALO.TFILTER({"3*","4*"},0,False),,PALO.DFILTER(PALO.SUBCUBE("Finance - Opening Balance","MCPL003","2018","2018-04-YTD",,"All Sub Accounts","All Divisions","All Departments","NGN","All Analysis",{"Net Posted Balance","Opening Net Balance"}),{"<>",0},,,,0),,PALO.SORT(1,0,,0,,0,0))
    

    It uses some UDF we have defined via a XLL.

    The lenght is 373

    private static dynamic ConvertFormulaToR1C1(string formulaA1)
    {
    	// formulaA1 < 256 chars
            return _oExcelApp.ConvertFormula(formulaA1, Excel.XlReferenceStyle.xlA1, Excel.XlReferenceStyle.xlR1C1);
    }
    
    internal static void AddFormulaToSpreadsheet(Excel.Range er, string formula)
    {
    	try
            {
              er.Formula = formula;
            }
            finally
            {
    	  ConnectHelper.setEnUSCulture();
            }
    }
    

    Wenn I tested the workaround Windows 7 was the king,

    but now Windows 10 is the new kid in town.

    with best regards

       Hendrik Schmieder

    Tuesday, July 3, 2018 1:50 PM
  • I can't set directly the FormulaArray property in this case.

    If I try it an exception is thrown.

    Tuesday, July 3, 2018 1:53 PM
  • Hello h_schmieder,

    If you have an array formula contains more than 255 characters, you could set the formula to the range value directly and then as your code design, try to simulate the operation to force it as an array formula.

    However, you should not Send F2 and Shift+Ctrl+Enter keys together. Send F2 first and then Send Shift+Ctrl+Enter.

    The simple code looks like.

     if (formula.Length > 255) {
                    er.Value = formula;
                    SendKeys("{F2}");
                    SendKeys("^+{ENTER}");
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by h_schmieder Friday, July 6, 2018 8:48 AM
    Wednesday, July 4, 2018 7:48 AM
  • Thanks Terry,

    that does helped me a lot.

    I now use this code for the bigger case

    // er is Microsoft.Office.Interop.Excel.Range
    
    // it is sufficient to set the formula on the very first cell
    er.Cells[1, 1].Value = formula;
    
    er.Worksheet.Activate();
    er.Select();
    
    // Important : ScreenUpdating must be true before sending F2
    ScreenUpdating = true;
    
    SendKeys("{F2}");
    ScreenUpdating = false;
    SendKeys("^+{ENTER}");
    
    This is also works if er is a whole column or a whole row


    • Marked as answer by h_schmieder Friday, July 6, 2018 8:48 AM
    • Edited by h_schmieder Friday, July 6, 2018 8:48 AM wrong time case
    Friday, July 6, 2018 8:48 AM
  • Hello,

    in 2018 this worked.

    But now this doesn't work anymore at least with  Office 2016 resp. Office 365.

    Only the first cell of the selected range get the Formula.

    Thursday, January 16, 2020 8:07 AM