none
Writing to a cell turns on ScreenUpdating in my VSTO add-in RRS feed

  • Question

  • I have a weird problem where Excel is behaving differently on my development machine and a testing machine.

    In my add-in, I've turned off ScreenUpdating in several places for long running processes. On my machine this works fine. On the testing machine, Excel sets `ScreenUpdating = true` as soon as I write to a cell.

    The following code demonstrates the issue for me.

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excel = Globals.ThisAddIn.Application;
    
        MessageBox.Show(excel.ScreenUpdating.ToString());
    
        excel.ScreenUpdating = false;
        MessageBox.Show(excel.ScreenUpdating.ToString());
    
        Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
        Worksheet w = (Worksheet)workbook.Worksheets[1];
        ((Range)w.Cells[1, 1]).Value = "Test";
        MessageBox.Show(excel.ScreenUpdating.ToString());
    }

    On my machine, opening Excel gives three message boxes saying

    "True", "False", "False".

    On the test machine they say

    "True", "False" and "True".

    I've also stepped through with a remote debugger and watched the `ScreenUpdating` property change immediately after the cell value is set. Further, this isn't the only thing that resets `ScreenUpdating`. Adding or removing a Worksheet or Workbook will also do this.

    The Excel version on each system is the same (14.0.6112.5000 (32-bit)).

    What could be causing this? How can I fix it so that Excel respects my settings?
    Monday, June 11, 2012 12:48 PM

Answers

  • Hi root45,

    An even better convention to follow than just setting the ScreenUpdating property back toTrue is to save the value of the ScreenUpdating property before you change it and set it back to that value when you are done. An important thing to remember when doing Office development is that your code is not going to be the only code running inside of a particular Office application. Add-ins might be running, as well as other code behind other documents, and so on. You need to think about how your code might affect other code also running inside of Excel.

    Reference:
    Eric Carter, Eric Lippert. Visual Studio for Office 2007. Addison-Wesley Professional; 1 edition (March 6, 2009)

    Regards,
    Fermin

    Tuesday, June 12, 2012 7:24 AM

All replies

  • This seems to be the result of another add-in. When I disable the second add-in, I no longer get the behavior and re-enabling it reproduces it.

    I suspect that the developers of said add-in are setting the ScreenUpdating property to false, then resetting it true when they're finished.

    • Edited by root45 Monday, June 11, 2012 1:38 PM
    Monday, June 11, 2012 1:38 PM
  • Hi root45,

    An even better convention to follow than just setting the ScreenUpdating property back toTrue is to save the value of the ScreenUpdating property before you change it and set it back to that value when you are done. An important thing to remember when doing Office development is that your code is not going to be the only code running inside of a particular Office application. Add-ins might be running, as well as other code behind other documents, and so on. You need to think about how your code might affect other code also running inside of Excel.

    Reference:
    Eric Carter, Eric Lippert. Visual Studio for Office 2007. Addison-Wesley Professional; 1 edition (March 6, 2009)

    Regards,
    Fermin

    Tuesday, June 12, 2012 7:24 AM