M
Matt Storz
I am trying to use C# .NET interop with Excel 2003 and I am running
into a couple of problems.
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 problems are:
1) When Excel is exited it crashes… If the workbook is closed before
exiting, Excel still crashes when you exit Excel. 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.
2) Every once in a while the Excel object model is unavailable in the
VBA Ticker event handler and the statement to set the A1 cell's value
causes an error pop-up to display saying "Run-time error ‘50290':
Application-defined or object-defined error". It usually happens
right away when selecting cells in sheet1. Selecting debug and
continuing just results in the pop-up displaying again. Adding a
resume next on error statement just before the cell's value assignment
at least allows the code to run without the pop-up displaying and
halting the code.
The C# project and Excel Workbook are available for download at:
http://www.shapescape.com/AClassLibrary.zip
Any thoughts about this will be greatly appreciated!
// 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
into a couple of problems.
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 problems are:
1) When Excel is exited it crashes… If the workbook is closed before
exiting, Excel still crashes when you exit Excel. 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.
2) Every once in a while the Excel object model is unavailable in the
VBA Ticker event handler and the statement to set the A1 cell's value
causes an error pop-up to display saying "Run-time error ‘50290':
Application-defined or object-defined error". It usually happens
right away when selecting cells in sheet1. Selecting debug and
continuing just results in the pop-up displaying again. Adding a
resume next on error statement just before the cell's value assignment
at least allows the code to run without the pop-up displaying and
halting the code.
The C# project and Excel Workbook are available for download at:
http://www.shapescape.com/AClassLibrary.zip
Any thoughts about this will be greatly appreciated!
// 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