I am experiencing an error in Excel while running a COM (Home made) Timer. Basically Excel instantiate the Timer, initializes it and starts it. The Timer then ticks every X milliseconds raising an event that Excel catches (pretty standard stuff). I am not using Excel itself as a timer because it does not tick faster than every second (which is too long for my purpose)
My issue is that if I click and hold on the spreadsheet while the event is raised by the timer, Excel crashes pretty bad. Unfortunately, the user needs (sometimes) to click in the spreadsheet and modify it while the timer is running.
I have seen somewhere that I could be using the IMessageFilter interface in my timer. This should make sure that if Excel is busy when the event is fired up the timer can just see this and act accordingly. However I was unable to implement it properly.
If somebody could help me out that would be great.
Here is the source code I am using:
In Excel I have a singleton that carries a WithEvents ExcelTimer.ExcelTimer object, here is the code of my singleton:
Option Explicit
Private Const m_sMODULE_NAME = "cTimerManager"
Public WithEvents oCsharpTimer As ExcelTimer.ExcelTimer
Private Sub Class_Initialize()
Set oCsharpTimer = New ExcelTimer.ExcelTimer
'The following two lines are called dynamically from somewhere else
'normally but for simplicity of my post I have put them here
oCsharpTimer.Initialize 500
oCsharpTimer.StartTimer
End Sub
Private Sub oCsharpTimer_TimeTickEvt(ByVal o As Variant, ByVal Time As String)
Const sPROCEDURE_NAME = "oCsharpTimer_TimeTickEvt"
On Error GoTo ErrorHandler
'"Send" confirmation with time to the COM object.
oCsharpTimer.TimeReceived Time
'Do whatever I wanna do when the event is trigger
CleanUp:
Exit Sub
ErrorHandler:
'My Error handling structure
If ProcessError(m_sMODULE_NAME, sPROCEDURE_NAME, Err) Then
Stop
Resume
Else
Resume Next
End If
End Sub
Here is the code for my COM Object:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Runtime.InteropServices;
using System.Windows.Forms;
namespace ExcelTimer
{
public delegate void EventTimeRaiser(object o, string Time);
//COM Interface
public interface ICOMExcelTimer
{
[DispId(1)]
void StartTimer();
[DispId(2)]
void StopTimer();
[DispId(3)]
void Initialize(int TimeInMilliseconds);
[DispId(4)]
void TimeReceived(string ReceivedTime);
}
//Event interface
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface ICOMExcelTimerEvent
{
[DispId(1000)]
void TimeTickEvt(object o, string Time);
}
[ClassInterface(ClassInterfaceType.None),
ComSourceInterfaces(typeof(ICOMExcelTimerEvent)),
ComVisible(true)]
public class ExcelTimer : ICOMExcelTimer, IMessageFilter
{
private event EventTimeRaiser TimeTickEvt;
private bool _started;
private bool _initialised;
private int _timeInMilliseconds;
private string _lastTimeReceived;
private Control _control;
private Thread _timerThread;
private IAsyncResult _AsynchronousResult;
[ComVisible(true)]
public void Initialize(int TimeInMilliSeconds)
{
//To be called by Excel to set which timer parameters it wants
_timeInMilliseconds = TimeInMilliSeconds;
_initialised = true;
//Make sure we clear the last confirmation received
//since we are re-initialising the object
_lastTimeReceived = "";
}
[ComVisible(true)]
public void TimeReceived(string ReceivedTime)
{
//Store the last time received. Excel calls this function
_lastTimeReceived = ReceivedTime;
}
public ExcelTimer()
{
_lastTimeReceived = "";
}
[ComVisible(true)]
//Start the Timer
public void StartTimer()
{
//If the timer has not been initialised just yet
if (!_initialised)
{
//Sends back an error message to Excel
TimeTickEvt(this, "Error: Timer Not Initialised");
return;
}
try
{
//Start the timer
_timerThread = new Thread(new ThreadStart(TimeTicking));
//Start the Thread
_started = true;
_timerThread.Start();
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@"C:\ErrorLog.txt", ex.Message + " - StartTimer - " + DateTime.Now.ToString("hh:mm:ss.f") + "\n");
}
}
[ComVisible(true)]
//Stop the timer
public void StopTimer()
{
//Stop the Thread
_timerThread.Abort();
//Change the status
_started = false;
}
private void TimeTicking()
{
string SentTime;
//As long as the timer is running
while (_started)
{
try
{
//Pause the timer for the right number of milliseconds
Thread.Sleep(_timeInMilliseconds);
SentTime = DateTime.Now.ToString("hh:mm:ss.ffff");
//########### The CODE Errors Here when Excel is busy! ###########
//Raise an event for Excel to grab with the time that the thread finished the sleep at.
OnTimeTick(SentTime);
//_lastTimeReceived is used so that if the link between Excel and the Thread is broken the thread stops after sometimes
//if no confirmation was received from Excel.
//If no last time was received just yet, we setup the last time received to the sent time
if (_lastTimeReceived.Equals(""))
{
_lastTimeReceived = SentTime;
}
//If the last time received is older than 10 x TimeInMilliseconds (in Seconds) we stop the timer.
else if (Convert.ToDateTime(_lastTimeReceived).AddSeconds(_timeInMilliseconds * 10 / 1000) < Convert.ToDateTime(SentTime))
{
OnTimeTick("Timer timed out. No Confirmation for more than " + _timeInMilliseconds * 10 / 1000 + " second(s).");
//Stop the timer because the thread has not received a last time recently
_started = false;
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@"C:\ErrorLog.txt", ex.Message + " - TimeTicking - " + DateTime.Now.ToString("hh:mm:ss.f") + "\n");
}
}
}
protected virtual void OnTimeTick(string Time)
{
try
{
if (Time != null)
{
//Raise the event
TimeTickEvt(this, Time);
}
}
catch (Exception ex)
{
System.IO.File.AppendAllText(@"C:\ErrorLog.txt", ex.Message + " - OnTimeTick - " + DateTime.Now.ToString("hh:mm:ss.f") + "\n");
}
}
}
}
