How to calculate selected Excel range using ExcelDnaUtil.Application selection?

Question

I'm writing a custom Excel add-in that assigns specific keyboard shortcuts to tasks such as number formatting for selected cells only. When I try to add a shortcut that will recalculate the selected range only, the entire sheet is re-calculated.

 public class Main : IExcelAddIn
    {

        public void AutoOpen()
        {
            dynamic app = ExcelDnaUtil.Application;
            app.OnKey("^N", "FormatNumbers");
            app.OnKey("^C", "CalcSelection");
        }

        public void AutoClose()
        {
        }

    }

    public class KeyboardShortcuts
    {

        public static void FormatNumbers()
        {
            dynamic app = ExcelDnaUtil.Application;
            dynamic selection = app.Selection;
            selection.NumberFormat = "#,##0;[Red]-#,##0";
        }

        public static void CalcSelection()
        {
            dynamic app = ExcelDnaUtil.Application;
            dynamic selection = app.Selection;
            selection.Calculate();
        }

    }

The FormatNumbers method works perfectly but the CalcSelection method forces the entire sheet to be recalculated rather than just the bits I've selected.

Would appreciate any suggestions to make this code work?

Answer 1

There are various quirks related to the Excel Range.Calculate call. You can find some good information on the FastExcel site from Charles Williams: https://www.decisionmodels.com/calcsecretsg.htm

If the book is in automatic calculation mode, it sounds like Range.Calculate will recalculate all volatile cells on all worksheets.