Workbook.SheetChange event not fired

[Lars]

Hi,

I'm using the Workbook.SheetChange event, but the event handler is only called for the first n events.
The C# code that I used to test this is at the end of this post. I use the Windows Input Simulator to insert the numbers 1 to 200 in cell A1. The output that I get is:

firing event 1 with value 1
firing event 2 with value 2
...
firing event 89 with value 89

So the event handler is not being called for events 90 to 200.

If I uncomment the line
Excel.Worksheet ws = Wb.ActiveSheet;
I only get 61 events being fired, even though I don't do anything with this variable ws.

The actual number of events being fired is not the same on all computers and depends on the amount of work that is performed in the program, so you might need to increase the 200 changes. I only added the key input simulator to make it easier to test, but the problem was there before I added it.

Any help is appreciated.

Lars


public class Class1 : IExcelAddIn
    {
        Excel.Application xlapp = (Excel.Application)ExcelDnaUtil.Application;
        int eventCounter = 0;

        public void AutoOpen()
        {
            xlapp.WorkbookActivate += xlapp_WorkbookActivate;
        }

        void xlapp_WorkbookActivate(Excel.Workbook Wb)
        {
            Wb.SheetChange += Wb_SheetChange;

            // if I uncomment the next line, even less events will be fired, although it seems irrelevant
            //Excel.Worksheet ws = Wb.ActiveSheet;

            // simulate keypresses to change the value of the selected cell A1
            InputSimulator s = new InputSimulator();
            for (int i = 1; i <= 200; i++)
            {
                s.Keyboard.TextEntry(i + "");
                s.Keyboard.KeyPress(VirtualKeyCode.UP);
            }
               
        }

        void Wb_SheetChange(object Sh, Excel.Range Target)
        {
            eventCounter++;

            string value = Target.Value.ToString();
            Debug.WriteLine("firing event " + eventCounter + " with value " + value);
        }

        public void AutoClose()
        {
            // do nothing
        }
    }

[Govert van Drimmelen]

HI Lars,

Maybe you need to keep an explicit reference to the Workbook to make sure the COM wrapper is not garbage collected.

As a test, just add a Workbook field to the class and assign the Wb to it in your WorkbookActivate handler.

If that works, one can dig into the COM details a bit more.

-Govert

[Govert van Drimmelen]

Can you also confirm that the last valued to get written to the sheet - thus that the problem is not with the input processing?

-Govert

[Lars]

Assigning the Wb to a Workbook field solves the problem.
Didn't know that it would be garbage collected, even though it has an event handler subscribed.

Thanks for your help 


Op dinsdag 30 juni 2015 17:03:36 UTC+2 schreef Govert van Drimmelen: