Changes to a range

[Editor's note] I've been on leave from Microsoft but I'm back now so postings should be more frequent again.

In VSTO 2.0 you can create a named range on a worksheet and write code behind that named range. Say you have a sheet called Sheet1 that has a named range called MyRange. In the VSTO 2.0 programming model, we create a Sheet1 class that you can write code in and we create a MyRange member in the Sheet1 class that you can handle events for, such as the change event. This event will fire any time the named range MyRange is changed.

Mohit Gupta, a developer on my team, recently answered some questions for a customer about handling changes on a named range. I thought the response was good, so I asked permission from him to post it and here it is:

[BEGIN MOHIT RESPONSE]

a) Can I get detailed information on the cell within the range that was changed?

Absolutely. The Change event that gets fired on the range has a “Target” parameter. This parameter contains the Excel Range object that has actually changed. You can use the get_Address(…) method to get the address of the range that has changed. 

b) Can I get the old value that this cell contained?

No. The Change event fires after the change has occurred and therefore the old value has already been lost at this stage. [Editor's note--you could potentially cache the old value yourself. I add a sample below Mohit's that shows the idea of caching the value of the named range in your initialize handler and keeping it around as a member variable on your Sheet1 class.]

c) Can I programmatically undo the change in case it does not meet some business rules?

Yes you can. Use the Application.Undo() method to undo the last change. Note, however, that this undo’s the complete change (not just a given cell or something). If the user edited more than one cells in the last gesture (e.g. by Copy-Paste), then all the edits get undone, and not just this cell.

d) Is there a sample that you can provide me that does something similar?

Below is sample code to do this. Note that you must use a semaphore to avoid re-entrance into the change event handler.

       private void Sheet1_Initialize(object sender, System.EventArgs e)
       {
            this.MyRange.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(MyRange_Change);
        }

        bool performingUndo = false;

        void MyRange_Change(Microsoft.Office.Interop.Excel.Range Target)
        {
            if (performingUndo)
                return;

            string cellAddress = Target.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1, null, null);
            object newValue = Target.Value2;
            bool dataIsValid = CheckValueForBusinessRules(newValue);

            if (!dataIsValid)
            {
                try
                {
                    MessageBox.Show("Data in " + cellAddress + " is not valid per rules. Undoing the change");
                    performingUndo = true;
                    this.Application.Undo();
                }
                finally
                {
                    performingUndo = false;
                }
            }
        }

        private bool CheckValueForBusinessRules(object newValue)
        {
            // apply rules to the new value here
            return false;
        }

[END MOHIT RESPONSE]

Here is some alternate code that uses the idea I mentioned before to cache the old value to undo rather than call Application.Undo().

       private void Sheet1_Initialize(object sender, System.EventArgs e)
       {
            this.MyRange.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(MyRange_Change);
oldValue = this.MyRange.Value2;
        }

        object oldValue = null;
bool performingUndo = false;

        void MyRange_Change(Microsoft.Office.Interop.Excel.Range Target)
        {
            if (performingUndo)
                return;

            string cellAddress = Target.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlR1C1, null, null);
            object newValue = Target.Value2;
            bool dataIsValid = CheckValueForBusinessRules(newValue);

            if (!dataIsValid)
            {
                try
                {
                    MessageBox.Show("Data in " + cellAddress + " is not valid per rules. Undoing the change");
                    performingUndo = true;
                    MyRange.Value2 = oldValue;
                }
                finally
                {
                    performingUndo = false;
                }
            }
else
{
oldValue = MyRange.Value2;
}
        }

        private bool CheckValueForBusinessRules(object newValue)
        {
            // apply rules to the new value here
            return false;
        }