Excel Crashing when running COM object with Thread

Question

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");
            }
        }        
    }
}

Answer 1

I can't help you with the C# coding, but if you would prefer to use Excel and a timer with more precision than 1 second, this can be done in VBA:

Public Declare Function timeBeginPeriod Lib "winmm.dll" (ByVal uPeriod As Long) As Long

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

You can then use the timeGetTime function from your subroutine to return the time in millisecond accuracy, if desired.

Credit where it's due:

http://www.excelforum.com/excel-programming-vba-macros/738087-using-milliseconds-in-vba.html?p=2748931&viewfull=1#post2748931