What are the benefits of the ExcelWorksheetEvents class?

Add-in Express™ Support Service
That's what is more important than anything else

What are the benefits of the ExcelWorksheetEvents class?
 
Nicholas Hebb




Posts: 94
Joined: 2008-08-22
What is the main advantage of using the ExcelWorksheetEvents class over creating your own event handlers? I wrote up the code sample below, and I couldn't tell the difference. Is it provided as a convenience to your customers, or are there other benefits?



#region ---- Addin module code ----------------------------------------

private Excel.Workbook _activeBook = null;
private Excel.Worksheet _sheet = null;
private ExcelWorksheetEvents _xlSheetEvents = null;


private void adxRibbonSinkEvents_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
    _sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
    if (_sheet == null) { return; }

    // Option 1: Use ExcelWorksheetEvents
    _xlSheetEvents = new ExcelWorksheetEvents(this);
    _xlSheetEvents.ConnectTo(_sheet, true);

    // Option 2: Create my own sheet_Change event handler
    _sheet.Change += new Excel.DocEvents_ChangeEventHandler(sheet_Change);

    _activeBook = (Excel.Workbook)_sheet.Parent;
    _activeBook.BeforeClose += new Excel.WorkbookEvents_BeforeCloseEventHandler(_activeBook_BeforeClose);
}

private void adxRibbonUnsinkEvents_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
    UnsinkEvents();
}


void sheet_Change(Excel.Range Target)
{
    // Option 1: Event handled in ExcelWorksheetEvents.ProcessChange()

    // Option 2: Handle event here
    string address = "[" + Target.get_AddressLocal(Office.MsoTriState.msoFalse, Office.MsoTriState.msoFalse, Excel.XlReferenceStyle.xlA1, Office.MsoTriState.msoFalse, Type.Missing) + "] ";
    System.Diagnostics.Debug.WriteLine("sheet_Change - " + address + Target.Value.ToString());
}


private void UnsinkEvents()
{
    // Option 1;
    if (_xlSheetEvents != null)
    {
        _xlSheetEvents.RemoveConnection();
        _xlSheetEvents.Dispose();
        _xlSheetEvents = null;
    }

    // Option 2:
    if (_sheet != null)
    {
        _sheet.Change -= sheet_Change;
        Marshal.ReleaseComObject(_sheet);
        _sheet = null;
    }

    if (_activeBook != null)
    {
        _activeBook.BeforeClose -= _activeBook_BeforeClose;
        Marshal.ReleaseComObject(_activeBook);
        _activeBook = null;
    }
}


void _activeBook_BeforeClose(ref bool Cancel)
{
    UnsinkEvents();
    if (_activeBook != null)
    {
        _activeBook.BeforeClose -= _activeBook_BeforeClose;
        Marshal.ReleaseComObject(_activeBook);
    }
}


private void AddinModule_AddinBeginShutdown(object sender, System.EventArgs e)
{
    if (_xlSheetEvents != null)
        _xlSheetEvents.Dispose();
}


#endregion

#region ---- ExcelWorksheetEvents ----------------------------------------

public override void ProcessChange(object target)
{
    string content = string.Empty;
    Excel.Range range = target as Excel.Range;
    if (range != null)
    {
        string address = "[" + range.get_AddressLocal(Office.MsoTriState.msoFalse, Office.MsoTriState.msoFalse, Excel.XlReferenceStyle.xlA1, Office.MsoTriState.msoFalse, Type.Missing) + "] ";
        System.Diagnostics.Debug.WriteLine("ProcessChange - " + address + range.Value.ToString());
    }
}

#endregion
Posted 08 Dec, 2011 01:54:22 Top
Andrei Smolin


Add-in Express team


Posts: 17113
Joined: 2006-05-11
Hi Nicholas,

The class provides a version-independent way to get connected to events in all Excel versions. Additionally, this class allows you to create several sets of event handlers; you connect this or that set to this or that sheet as your business logic requires.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Dec, 2011 02:07:20 Top
Nicholas Hebb




Posts: 94
Joined: 2008-08-22
Hi Andrei,

OK, thank you for clarifying that. I was curious about one more thing, though. Why does ExcelWorksheetEvents.ProcessChange(object target) pass target as an object instead of as an Excel.Range? Can it handle changes to other types of Excel objects?
Posted 08 Dec, 2011 03:38:49 Top
Andrei Smolin


Add-in Express team


Posts: 17113
Joined: 2006-05-11
Hi Nicholas,

If you pass an "Excel.Range" and the code contains "using Microsoft.Office.Interop.Excel;" you get a compile error. Also, imagine an add-in project that doesn't use interops at all.

Nicholas Hebb writes:
Can it handle changes to other types of Excel objects?


No. This is the Worksheet.Change event.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Dec, 2011 03:56:04 Top
Nicholas Hebb




Posts: 94
Joined: 2008-08-22
Andrei Smolin writes:
No. This is the Worksheet.Change event.


I was hoping for the long shot that you had figured out how to detect changes to other objects on the worksheet, such as autoshapes. :)
Posted 08 Dec, 2011 04:40:19 Top
Andrei Smolin


Add-in Express team


Posts: 17113
Joined: 2006-05-11
Hi Nicholas,

Do you need to know if some properties of the selected autoshape were modified by the user?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 09 Dec, 2011 03:57:20 Top
Nicholas Hebb




Posts: 94
Joined: 2008-08-22
Actually, now that I think about, what would be more useful would be to detect the SelectionChange for objects other than ranges. But, in the few cases that it would have been useful, I have already started coding around it. I would like to display a TaskPane when shapes are selected. However, I'm committed to releasing in a few weeks, so if there is a way to detect this, it's something I'd need to look at a later date.
Posted 09 Dec, 2011 05:16:18 Top
Andrei Smolin


Add-in Express team


Posts: 17113
Joined: 2006-05-11
Hi Nicholas,

Nicholas Hebb writes:
Actually, now that I think about, what would be more useful would be to detect the SelectionChange for objects other than ranges.


This requires checking ExcelApp.Selection in CommandBars.OnUpdate. We are going to publish a new build providing that event in Excel add-ins next week. You'll also need to use this event to check if a shape has been changed.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 09 Dec, 2011 06:24:11 Top
Nicholas Hebb




Posts: 94
Joined: 2008-08-22
Excellent. I look forward to this.
Posted 09 Dec, 2011 06:31:34 Top
Andrei Smolin


Add-in Express team


Posts: 17113
Joined: 2006-05-11
Hi Nicholas,

The new build is published on the web site, see here.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 19 Dec, 2011 02:55:43 Top