locked
How can we stop all excels formula calculation before worksheet open event RRS feed

  • Question

  • we know we can set formula calculation mode after worksheet open event only

    Application.Calculation = XlCalculation.xlCalculationManual;

    some documents when opening locally in excel 2016 is calling all formulas before addin start up event, worksheet open  event is after addin start up so i can not set manual calculation before worksheet open

    How does a vsto addin developer handle this scenario?.All i am looking is something that say excel application to stop all formula calculations?, i put in calculation manual code in addin start up and even in udf automation class connect , but that will throw exception because calculation set can be done only after worksheet open event

    both code dont work

      private void ThisAddInStartup(object sender, EventArgs e)
       {

    Application.Calculation = XlCalculation.xlCalculationManual;

    }

      public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
            {
    application.Calculation = XlCalculation.xlCalculationManual;
            }

    gj

    Thursday, October 5, 2017 6:36 PM

All replies

  • In excel 2016 some excel docs are calculating formulas on open .I want to stop it.But i can set calculation option to manual only after workbook is activated, but before that my formula get called.Is  there a way to delay formula calculation or way to cancel formula call before workook is activated. Any propertie that set to not calculate other than manual when using interop excel with vsto addin?
    • Merged by Chenchen Li Friday, October 6, 2017 6:15 AM same issue
    Tuesday, September 26, 2017 3:37 PM
  • Hello,

    Please test if it works when you set Application.Calculation Property (Excel) into Manual in the Document.Open Event:

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                this.Application.WorkbookOpen += Application_WorkbookOpen;
            }
            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                this.Application.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
            }

    Regards,

    Celeste


    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.

    Wednesday, September 27, 2017 2:53 AM
  • Thanks for taking time to respond,apptreciate it..I have tried it before, but the issue is automation  formula is called  before worksheet open,so all formula will get called.

    Only way i can set  calculation option  before automation call is  to do this before automation call in from addin start up,but the issue here is that it will create an  extra workbook

     ThisApplication.Workbooks.Add(Type.Missing);
      ThisApplication.Calculation = XlCalculation.xlCalculationManual;

    I extracted and checked the excel doc, looked worksheets.xml , ReCalculateOnOpen propertie is not set, still don't know why office 2016 unnecessary calculate formula on open,but it is fine on office 2013, it doesn't do that calculate on open



    Wednesday, September 27, 2017 12:53 PM
  • Here are my questions

    1)Why excel calculate formulas  before worsheet open event.Because  of this i can not set calculation option to manual before excel formula call

    2)I dont see any visual propertie in excel document to calculate formula on open.Not sure what is triggering this in office 2016?

    Any help is appreciated, thanks

    Wednesday, September 27, 2017 5:50 PM
  • Tried Application.CheckAbort(); even that is not stoping UDF call and other formula caluclation

    I need  some thing to stop calculation on add in start up, but before workshee open

    Thursday, September 28, 2017 4:20 AM
  • Hello,

    >>the issue is automation  formula is called  before worksheet open,so all formula will get called.

    Could you please share how you create the formula?

    >>I need  some thing to stop calculation on add in start up, but before workshee open

    I have tested several events in Workbook Events and Application events. Unfortunately, no event would meet your requirement.

    If you create a new workbooks and set the calculation mode before opening this workbook, would the UDF in your workbook be calculated? How do you do if it works? I create a new workbook at add-in startup event and try to change the calculation mode, but I get HRESULT exception. The exception is the same as I simply set the calculation mode when add-in starts.

    Regards,

    Celeste


    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.

    Thursday, September 28, 2017 7:09 AM

  • Formulas are udf formulas, they  are put in automation class project which is separated from addin project

         [ComVisible(true)]
         public string bText(string formula)
        {

        }


        private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {

            this.Application.Workbooks.Add();
                this.Application.Calculation = XlCalculation.xlCalculationManual;
                this.Application.CalculateBeforeSave = false;
                this.Application.WorkbookOpen += Application_WorkbookOpen;
            }
            private void Application_WorkbookOpen(Excel.Workbook Wb)
            {
                this.Application.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
            }


    I dont want to do this because it create additional workheet  this.Application.Workbooks.Add();, but this is the only way to set Calculation  before workaper open

    Thursday, September 28, 2017 12:56 PM
  • Hello,

    I think you may unload the add-in firstly to disconnect the UDF and change the calculation mode and then reload it.

    You could use the following code to unload COM add-ins or Excel add-ins (xla, xlam, xll)

                Office.COMAddIn comAddIn = this.Application.COMAddIns.Item("AddInName");
                comAddIn.Connect = false;
    
                Excel.AddIn addIn = this.Application.AddIns.Item["AddInName"];
                addIn.Installed = false;

    Regards,

    Celeste


    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.

    Friday, September 29, 2017 6:10 AM
  • i was trying to set default formula value by looping, but value get set, but formula is coming as text value instead of actual formula in formula bar




      private void SetCachedFormulaValues()
            {


                if (ThisApplication.ActiveWorkbook.Sheets != null)
                {
                    foreach (Worksheet sheet in ThisApplication.ActiveWorkbook.Sheets)
                    {
                        if (sheet != null && sheet.UsedRange != null)
                        {
                            var gj = sheet.Name;
                            Microsoft.Office.Interop.Excel.Range exlRange = sheet.UsedRange;
                            if (exlRange != null && exlRange.HasFormula != null)
                            {
                                Object value = exlRange.HasFormula;

                                if (Convert.IsDBNull(value) || Convert.ToBoolean(value))
                                {


                                    foreach (Excel.Range Celgj in exlRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Missing.Value))
                                    {
                                        string formula = Celgj.Formula;

                                        if (_dicOrginalFormulaAndValues.ContainsKey(formula))
                                        {
                                              exlRange.Cells[Celgj.Row, Celgj.Column].Formula =formula; // restoring the formula
                                            exlRange.Cells[Celgj.Row, Celgj.Column].Value = _dicOrginalFormulaAndValues[formula];
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                                                                      
    • Merged by Chenchen Li Monday, October 9, 2017 9:55 AM one issue
    Tuesday, October 3, 2017 3:00 AM
  • In disconnected mode of my addin ,i want to default to orginal  formula value   than new value when udf is called.

    In the below function checking exlRange.Value2 set formula to orginal value than new value,it do the job i want, but

    i dont understand how  just  checking   exlRange.Value2  lset to orginal formula value which is unbelievable to me.Is it ok to us this as my fix?


    [ComVisible(true)]

            public double Amount(string formula)
            {
                if (ThisApplication.ActiveWorkbook.Sheets != null)
                {
                    foreach (Worksheet sheet in ThisApplication.ActiveWorkbook.Sheets)
                    {
                        if (sheet != null && sheet.UsedRange != null)
                        {
                            var gj = sheet.Name;
                            Microsoft.Office.Interop.Excel.Range exlRange = sheet.UsedRange;
                            if (exlRange != null && exlRange.HasFormula != null)
                            {
                                Object value = exlRange.HasFormula;

                                if (Convert.IsDBNull(value) || Convert.ToBoolean(value))
                                {
                                    if (exlRange.Value2 != null)
                                    {

                                    }

                                    break;
                                }
                            }
                        }
                    }
                }

                return 77;
            }

    • Merged by Chenchen Li Monday, October 9, 2017 9:56 AM one issue
    Tuesday, October 3, 2017 3:10 AM
  • Hello,

    It seems that your previous issue has been resolved: https://social.msdn.microsoft.com/Forums/vstudio/en-US/b19e81b1-2494-4b15-8e8a-874243c7fb98/how-to-stop-excel-from-recalculating-formulas-on-openinterop-excel-vsto-addin?forum=exceldev We would be appreciate if you could share your solution and mark it as answer to close that thread.

    According to the code, you are developing a document level customization. Is the customization based on specific document?  Could you share your document here? What is your_dicOrginalFormulaAndValues? I suggest you share complete code or document here so that we could reproduce your issue.

    Regards,

    Celeste


    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.

    Wednesday, October 4, 2017 3:11 AM
  • Hello,

    According to the code, you are developing a document customization. How do you create the function? Is the code in ThisWorkbook class or a new created class? How do you use the UDF?

    Please share detail steps here so that we could reproduce your issue.

    Regards,

    Celeste


    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.

    Wednesday, October 4, 2017 3:15 AM
  • The above code is not in addin  project ,its in the udf automation class

    ComVisible(true)]        

    public double Amount(string formula)

    I was trying to cancel the new  value 77 as example and  put the orginal formula value from cell.

    if (exlRange.Value2 != null) this statment do similar to e.cancel and default formula to orginal value, that is what i wanted ,but i dont undrstand why just  using code    if (exlRange.Value2 != null) do that?Is it ok to use that code?

    Wednesday, October 4, 2017 4:15 AM
  • This doesnt work mainly because  excel call formulas before worksheet open and just after addin start up,so excel will anyway call formula on opening excel, even if we disable connect  still formula will call but will not evaluate.So the problem here is how can we cancel formula calculation other than using manual calculation option.My other threads are because this option dont work.So i am trying to get cached sheet value and try to set it again
    Wednesday, October 4, 2017 4:09 PM
  • Here is the  full sample code you can try.Please add few formulas and try it

    1)When i set value for formula, why actual formula change to text in formula bar ?

    2)When i loop to set ,is any cell property exist to cancel formula calculation for that cell?

     private Dictionary<string, object> _dicOrginalFormulaAndValues;

            private void ThisApplicationWorkbookOpen(Workbook wb)
            {

                _dicOrginalFormulaAndValues = new Dictionary<string, object>();
                LoadOrginalFormula();
            }

            private void LoadOrginalFormula()
            {

    _dicOrginalFormulaAndValues = new Dictionary<string, object>();

                if (ThisApplication.ActiveWorkbook.Sheets != null)
                {
                    foreach (Worksheet sheet in ThisApplication.ActiveWorkbook.Sheets)
                    {
                        if (sheet != null && sheet.UsedRange != null)
                        {
                            var gj = sheet.Name;
                            Microsoft.Office.Interop.Excel.Range exlRange = sheet.UsedRange;
                            if (exlRange != null && exlRange.HasFormula != null)
                            {
                                Object value = exlRange.HasFormula;

                                if (Convert.IsDBNull(value) || Convert.ToBoolean(value))
                                {
                                    string isFormulaPresent = "YES";
                                  
                                    foreach (Excel.Range Celgj in exlRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Missing.Value))
                                    {
                                        string formula = Celgj.Formula;
                                        string text = Celgj.Text;
                                        if (!_dicOrginalFormulaAndValues.ContainsKey(formula))
                                        {
                                            _dicOrginalFormulaAndValues.Add(formula, text);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                
            }

            private void SetCachedFormulaValues()
            {


                if (ThisApplication.ActiveWorkbook.Sheets != null)
                {
                    foreach (Worksheet sheet in ThisApplication.ActiveWorkbook.Sheets)
                    {
                        if (sheet != null && sheet.UsedRange != null)
                        {
                            var gj = sheet.Name;
                            Microsoft.Office.Interop.Excel.Range exlRange = sheet.UsedRange;
                            if (exlRange != null && exlRange.HasFormula != null)
                            {
                                Object value = exlRange.HasFormula;

                                if (Convert.IsDBNull(value) || Convert.ToBoolean(value))
                                {
                                  

                                    foreach (Excel.Range Celgj in exlRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Missing.Value))
                                    {
                                        string formula = Celgj.Formula;
                                        
                                        if (_dicOrginalFormulaAndValues.ContainsKey(formula))
                                        {
                                            Celgj.Value2 = _dicOrginalFormulaAndValues[formula];
                                         
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

            }


    Wednesday, October 4, 2017 4:23 PM
  • Hello,

    AFAIK, to create UDFs, we could create automation add-ins or XLL add-ins.  

    How do you create the automation class? Do you create an automation add-in like How To  Create a Visual Basic Automation Add-in for Excel Worksheet Functions or  https://www.codeproject.com/Articles/606446/UsingplusC-plus-NETplusUserplusDefinedplusFuncti?

    I failed to get your code or your udf to work and i have no idea how you create the UDF. According to the code you shared, it seems they are in the same project. Could you please share detail steps?

    Regards,

    Celeste


    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.

    Friday, October 6, 2017 8:18 AM
  • Thanks for your prompt replies

    here is the sample structure of project,download from here

    https://files.fm/u/thzzcc9p

    I just want to see how we can  stop excel formula calculation before worksheet open event

    You can also manually register automation dll like this

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe "C:\gj\tfs\tesgj\MyExcelAutomation\obj\Debug\MyExcelAutomation.dll" /tlb: "C:\gj\tfs\tesgj\MyExcelAutomation\obj\Debug\MyExcelAutomation.tlb" /codebase "C:\gj\tfs\tesgj\MyExcelAutomation\obj\Debug"

    Friday, October 6, 2017 4:23 PM
  • Hello,

    Thanks for the sharing. As the issue is complicated, I'm trying to involve some senior engineers into this issue. It will take some time. Your patience will be greatly appreciated. Besides, i would merge your several threads as they are derived from one issue. 

    Regards,

    Celeste


    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.

    Monday, October 9, 2017 9:58 AM
  • Thanks for organizing the thread and appreciate your help.

    Some of our files call formulas on  excel 2016 during open, but it work normally not calling formula on open in excel 2013 and 2010.

    Also in office 2016 , the udf formulas are called before addin start up when i open file  locally.I just want to stop calculation when files are open locally, my sample project has the structure of my addin and udf automation class. IN my case, udf get called first, then addin start up, then only workbook open event happens and then only i can set calculation.May be if i can trigger worksheet open before ufs formula call, that might help

    Monday, October 9, 2017 1:33 PM
  • Hi Philip,

    As you have found, we could not set “Application.Calculation” before workbooks open.

    In my option, Application.Calculation returns or sets a XlCalculation value that represents the calculation mode of the workbook. If there is no workbook, I am afraid we could not set any value for Calculation. And that’s the reason why it works when you add a new workbook.

    For your scenario, if you try to set Application.Calculation during Application_WorkbookOpen, the formula will be calculated before WorkbookOpen fired. I am afraid we could not change the event fire order since this is controlled by Excel Application.

    The possible solution seems to be your current workaround.

    >> I dont want to do this because it create additional workheet  this.Application.Workbooks.Add()

    Do you get an additional workbook while you open an exist workbook?

    As my test, if I launch AddIn from VS, it would not create an additional workbook. If I open an exist workbook, it will create a new workbook by side.

    If so, I suggest you try below code whether it works for you. I test under Excel 2016, it would not create an additional workbook.

            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                if (Application.Workbooks.Count == 0)
                {
                    Application.Workbooks.Add();
                }
                Application.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
            }
    

    Best Regards,

    Edward


    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, October 10, 2017 5:38 AM
  • I appreciate all your help and your quick responses.

    We can not create a dummy worksheet as solution which will look bad to customer.

    So we understood we cant stop formulas calculation before worksheet open.

    BUt now next step is  how to cancel formula calculation before worksheet open

    This seems to cancel  formula calculation, but we dont understand how this do that

      if (_application.Caller.Worksheet.Cells(_application.Caller.Row, _application.Caller.Column).Value2 != null)
                {
                }

    You can put below in my previous sample code and you can see new value will not set because of the if condition.That work as a solution for us,but can you ask your developer if its safe to use?

            [ComVisible(true)]
            public string MyText(string formula)
            {
              //  OfficeLogger.Log("gjtext");
                //CancelFormulaValue();
                if (_application.Caller.Worksheet.Cells(_application.Caller.Row, _application.Caller.Column).Value2 != null)
                {
                }

                return "gj";
            }


            [ComVisible(true)]
            public double MyAmount(string formula)
            {
            //    OfficeLogger.Log("gjamount");
                //CancelFormulaValue();
                if (_application.Caller.Worksheet.Cells(_application.Caller.Row, _application.Caller.Column).Value2 != null)
                {
                }
                return 9;
            }

    • Proposed as answer by Tony---- Friday, October 13, 2017 10:04 AM
    Thursday, October 12, 2017 3:07 PM
  • Hi Philip,

    Do you mean my above code will create dummy worksheet?

    Thanks for sharing the workaround.

    To be honesty, there is no internal channel for us to contact Office Developer Team.

    If you want to get support from Developer Team, I will suggest you contacting the Microsoft professional support so that our engineers can work closely with you to troubleshoot this issue.

    If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Best Regards,

    Edward


    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.


    Friday, October 13, 2017 10:03 AM
  • No  i didnt mean  that above code create worksheet.I  like to know why this statement cancel the setting of the formula value in a udf function.You can put it and see


      if (_application.Caller.Worksheet.Cells(_application.Caller.Row, _application.Caller.Column).Value2 != null)
                {
                }

    Just put that line in the udf function in the project i send to you

    initliazing Value2  properties is doing something to cancel seting of formula value in excel 2016, can you explain why?



    Friday, October 13, 2017 4:21 PM
  • Hi Philip,

    It’s sad that we do not the depth implementation of this except Office Product Developer Team.

    I suggest you consider personal support channel.

    Best Regards,

    Edward


    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.

    Monday, October 16, 2017 1:53 AM