|
|
|
Matt Storz
Posts: 1
Joined: 2004-12-01
|
I recently purchased Add-in Express. I am running into a problem with using a .NET object through COM interop with events.
In this example, a .NET class called Ticker is in a .NET assembly dll
called AClassLibrary. Ticker exposes a COM interface with a GetTick
and a Dispose method. It also exposes a COM event with one event
called TickEvent. Ticker references a System.Threading.Timer and
handles the timer event from it every second. The timer event handler
increments the Ticker.tick integer data member and raises the COM
event.
The AClassLibrary.tlb is referenced in the Excel VBA. In the VBA
code, an instance of Ticker is created with events. The event from
Ticker is handled and calls Ticker.GetTick and displays the tick value
in the A1 cell of Sheet1. This value changes in the A1 cell every
second as it should.
The problem is when Excel is exited it crashes?Â?Ð?? If the workbook is closed before exiting, Excel still crashes when you exit. If the TickEvent
call in the C# code is commented out so Ticker does not send any
events to Excel, Excel does not crash on exit. If the WithEvents is
removed from the VBA code so that the VBA event handler is not called,
Excel does not crash on exit.
I am running Excel 2003 with XP Professional and I have all the latest updates from Microsoft.
The C# project and Excel Workbook are available for download at:
http://www.shapescape.com/AClassLibrary.zip
// C# code in Ticker.cs -------------------------------
using System;
using System.Threading;
using System.Runtime.InteropServices;
namespace AClassLibrary
{
public delegate void TickEventHandler();
#region Events raised by COM class
[Guid("B846A796-34E3-4B7E-BDF5-114B875CDCCE")]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface ITickerEvents
{
[DispId(1)] void TickEvent();
}
#endregion
#region Interface published by COM class
[Guid("42EBAA0E-F2F3-499B-9143-6AE919C00F92")]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface ITicker
{
[DispId(1)] int GetTick();
[DispId(2)] void Dispose();
}
#endregion
[Guid("436E5AB1-ED28-4E4E-B675-98D7473703C7")]
[ProgId("AClassLibrary.Ticker")]
[ClassInterface(ClassInterfaceType.None)]
[ComSourceInterfaces(typeof(ITickerEvents))]
public class Ticker : ITicker, IDisposable
{
private bool _disposed = false;
private int _tick = 0;
private Timer _timer;
private TimerCallback _timerDelegate;
public event TickEventHandler TickEvent;
public Ticker() : base()
{
_timerDelegate =
new TimerCallback(processTimeEvent);
_timer = new Timer(_timerDelegate, null, 0, 1000);
}
~Ticker()
{
Dispose(false);
}
private void processTimeEvent(Object stateInfo)
{
_tick++;
if (TickEvent != null)
{
TickEvent();
}
}
public int GetTick()
{
return _tick;
}
#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if(!_disposed)
{
if(disposing)
{
_timer.Dispose();
_timer = null;
_timerDelegate = null;
}
}
_disposed = true;
}
#endregion
}
}
?Â?Ð?è VBA code in ThisWorkbook module ------------------------
Private WithEvents ticker As AClassLibrary.Ticker
Private Sub Workbook_Open()
Set ticker = New AClassLibrary.Ticker
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = False Then
ticker.Dispose
Set ticker = Nothing
End If
End Sub
Private Sub ticker_TickEvent()
Dim rng As Range
Set rng = Application.Worksheets("Sheet1").Cells(1, 1)
rng.Value = ticker.GetTick
End Sub
|
|
|
Posted 01 Dec, 2004 17:55:15
|
|
Top
|
|
|
Sven Heitmann
Posts: 193
Joined: 2004-08-23
|
Edit:
Ok, I've tested it myself... debug won't work because it is no bug the JIT Debuger can handle :-/
I think it is caused by the Threadmodel Timer is using...
When I remove Your private member timer, there is no problem with WithEvents.Best regards,
Sven Heitmann |
|
|
Posted 02 Dec, 2004 05:09:37
|
|
Top
|
|
|
Sergey Grischenko
Add-in Express team
Posts: 7233
Joined: 2004-07-05
|
Hi Matt,
Sorry for delay. I have tested your code and I get the same.
I need some time to find a proper solution and I will flag you tomorrow. |
|
|
Posted 02 Dec, 2004 12:09:40
|
|
Top
|
|
|
Matt Storz
Guest
|
Guys, Thanks for looking at this... I am trying all kinds of things to get around this problem but no luck yet :(
The reason for my wanting to do this is to connect to a .NET library of classes that have events (exposed through RCW/CCW) that show up in Excel VBA so that the user can write event handlers for them. After the user creates an object from the libarary with events, the events associated with the object show up in the VBA event drop down list which is what I want. Everything works just fine until Excel is exited and crashes.
Thinking that it is a threading issues, I am trying to have Excel periodically poll the .NET objects from a timer in Excel but this makes Excel shutdown silently.
|
|
|
Posted 02 Dec, 2004 13:58:52
|
|
Top
|
|
|
Sergey Grischenko
Add-in Express team
Posts: 7233
Joined: 2004-07-05
|
Hi Matt,
I still have got nothing to get around this problem. Didn't you think about another approach to solve your issue? It is difficult for me to advice you anything, because I haven't got a clear notion of the functionality of your application. |
|
|
Posted 03 Dec, 2004 09:52:39
|
|
Top
|
|
|
Matt Storz
Guest
|
What I am trying to do is described in the book "Microsoft .NET Development for Microsoft Office" section 4.2 "Managed CCW in VBA". Unfortunately the author did not give an example using events (maybe he knows something we don't :)
I am trying to use classes in a .NET class library in Excel VBA. This library has a set of wonderful classes that do all kinds of great things :) Some of them have events that I want to write event handlers for in Excel VBA. Adding COM interfaces to the .NET classes works great. The Intellisense in Excel VBA sees all of the methods and events exposed by these classes through their COM interfaces. The Excel VBA user can create instances of these classes with events and call methods exposed by them and select their events from the drop down list in VBA and write code to handle them. This all works great until the user exits Excel and then CRASH! BTW: Simply calling an event that does nothing from another thread will cause Excel to crash on exit.
I was able to avoid the crash by adding a timer to Excel VBA and have it periodically call an event server singleton in the .NET assembly that owns an array of references to other .NET objects that want to fire an event to Excel VBA... So when a .NET object wants to fire an event to Excel VBA, instead of firing the event, it adds itself to the event server which gets called periodically from the Excel VBA timer. During this call from Excel, the event server removes each .NET object reference from the array and tells them to fire their events directly to Excel. So the events are all fired in Excel's thread. I do not like this solution at all because of the wasted CPU time Excel is polling the event server for events to fire.
I then had another idea this morning... create the event server as an RTD server and then when the .NET objects want to fire an event to Excel they tell the event server which places them into the array, like the solution above, and then the event server fires the RTD notify event... Excel calls back using the RTD refresh data call to get the data and that's when the event server tells all the .NET objects in its array to fire their events directly to Excel... This works great except that the Excel object model is locked during the RTD refresh data call and all of the event handlers in VBA cannot access the Excel object model. This is very unfortunate because all of the Excel VBA event handlers want to do someting with the Excel object model like place data in cells, update charts, etc.
One way to get event driven data from the .NET objects into Excel VBA is to put the RTD interfaces on each one... but then the .NET objects are not created in Excel VBA to make public calls on. I do not know how to get the object that fired the RTD notify after the call to RTD so that public methods could be called on it in. All of the data from the object that the user could possible want would have to be returned by the RTD function even though the user might just want a small subset of it. Also there is no Intellisense to guide the user on the usage of the class.
Isn't there some way to marshal a .NET event (using the IConnectionPoint interface) through COM so that it executes the Excel VBA event handler in Excel's thread instead of the event caller's thread? Or is there some other way to hijack Excel's thread without having to use polling?
|
|
|
Posted 03 Dec, 2004 13:39:07
|
|
Top
|
|
|
Sergey Grischenko
Add-in Express team
Posts: 7233
Joined: 2004-07-05
|
Hi Matt,
Thanks for the detailed description of your application.
I see you have made a great deal of research. I will try to help you to find a solution and will let you know as soon as I find out anything. |
|
|
Posted 06 Dec, 2004 10:08:43
|
|
Top
|
|
|
Guest
Guest
|
|
|
Posted 06 Dec, 2004 13:43:49
|
|
Top
|
|
|
Guest
Guest
|
|
I'm glad someone found a solution. =) |
|
|
Posted 06 Dec, 2004 22:19:16
|
|
Top
|
|
|
Sergey Grischenko
Add-in Express team
Posts: 7233
Joined: 2004-07-05
|
Hi Matt,
It is an interesting solution. Thank you for your code. |
|
|
Posted 07 Dec, 2004 07:33:32
|
|
Top
|
|
Forums
Topics
Search
Help
Login
Register