Using Timer in XLL - Add-in Express .NET forum

nwein

Posts: 577
Joined: 2011-03-28

I'm aware of the http://www.add-in-express.com/docs/net-excel-udf-tips.php#xll-using-timer and fully understand it, however I wanted to know if the use of Timer not in a udf, in the XLL is also not recommended.
i.e are there any side effects to me having a timer that is running every x seconds doing it's own thing, unrelated to Excel at all?
For example


private System.Windows.Forms.Timer _myTimer = new System.Windows.Forms.Timer();

private void MyXLLModuleOnInitialize(object sender, EventArgs e)
{	
	_myTimer.Enabled = true;
	_myTimer.Interval = 5000;
	_myTimer.Tick += OnTimerTick;
	_myTimer.Start();
}

private static void OnTimerTick(object sender, EventArgs e)
{
	Debug.WriteLine("Tick");
}

Also, for the Timer, is there any preference out of the different system ones (System.Windows.Forms.Timer, System.Threading or System.Timers) as to which one is better to use? (as you can see I've chosen a System.Windows.Forms.Timer) perhaps because of different apartments, threads or other reasoning?

nwein

Posts: 577
Joined: 2011-03-28

I'd also like to mention that I'm aware that I can use the OnTime event, but my question is more generic (especially since in my case I need two timers)

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Nir,

Excel user-defined functions are executed in a special context. The timer event may occur in a different context causing your XLL to break and Excel to crash.

In a number of samples and posts we described the idea and technique allowing you switch the context when performing a call from XLL to a COM add-in, see e.g. http://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/. For a call in the different direction, there're three ways to prepare the context: 1) call ExcelApp.Evaluate() method, 2) recalculate the range, 3) use the XLLModule.OnTime. All other approaches may end in a crash.

I would look into inventing an algorithm using one or two timers and several flags to manage the XLLModule.Interval property and XLLModule.OnTime to provide you with two events as if they are generated by two real timers. I believe this is possible.

Andrei Smolin
Add-in Express Team Leader

nwein

Posts: 577
Joined: 2011-03-28

Thanks for the response Andrei.
Either way that I choose, how would I go about calling/invoking calls to the COM addin if i'm completely stateless?
If i'm not intending on calling the COM from my UDF, where would I be calling it from?
I guess I could just create the timers in my COM addin. I just didn't think I'd need a COM addin for that purpose (trying to keep it simple)

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

nwein writes:
Either way that I choose, how would I go about calling/invoking calls to the COM addin if i'm completely stateless?

Not sure that I understand this. For an XLL to perform a COM call outside of the XLL execution context, you use SendMessage/OnSendMessage machinery: 1) when an XLL function is called, XLL calls a method in a COM add-in, 2) the method calls SendMessage and quits; 3) the XLL function quits and here ends the XLL execution context, 4) the OnSendMessage event of the module fires in the COM Add-in context; 5) you perform the COM call.

nwein writes:
If i'm not intending on calling the COM from my UDF, where would I be calling it from?

In a COM add-in, see above.

nwein writes:
I guess I could just create the timers in my COM addin. I just didn't think I'd need a COM addin for that purpose (trying to keep it simple)

You can create timers in a COM add-in. To avoid creating COM add-in, consider using a timer in the XLL: when the timer fires, set the Interval property of the XLL module. I believe it is possible to use this approach for developing an algorithm invoking two different methods at the specified intervals.

Andrei Smolin
Add-in Express Team Leader

nwein

Posts: 577
Joined: 2011-03-28

My original question inferred that I'm not tying my timers to any UDF (the initialization and setup of the timers happens only in the XLL initialize event and has nothing to do with any UDF).

Not sure that I understand this. For an XLL to perform a COM call outside of the XLL execution context, you use SendMessage/OnSendMessage machinery: 1) when an XLL function is called, XLL calls a method in a COM add-in, 2) the method calls SendMessage and quits; 3) the XLL function quits and here ends the XLL execution context, 4) the OnSendMessage event of the module fires in the COM Add-in context; 5) you perform the COM call.

Maybe I wasn't clear, but the whole purpose of the timers was to not be dependant on any UDF.
Imagine that I don't have any UDFs in my XLL (it's only hypothetical of course), that's the scenario I'm trying to describe.
I don't want to trigger calls to the COM module when a function is being called, I want to make sure that my timer tick gets executed every tick regardless if I have any udfs in my sheet or not.

The Interval setting in the XLL (without a COM add-in) is an approach I'm trying to go with, but as you can see in my previous post, there's a small bug there (I can overcome it, but it's a little annoying :) )
Thanks anyways.

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Nir,

That is, you are using a timer that doesn't execute COM-related and XLL-related calls? Say, you check the registry, some files or execute a Web call, correct? If so, then you probably can use any timer that suits your needs.

One moment though. Imagine a timer event that occurs on the main thread in the middle of the recalculation cycle. To execute the event handler, Windows stops whatever is currently executing in the thread, executes your code and then continues executing the previously stopped code. I don't know if Excel is prepared for this scenario.

Andrei Smolin
Add-in Express Team Leader

nwein

Posts: 577
Joined: 2011-03-28

That is, you are using a timer that doesn't execute COM-related and XLL-related calls? Say, you check the registry, some files or execute a Web call, correct?

100% Correct.

As for your second point - that was the reason for my question. Would running System.Threading.Timer (i.e. timer execution is done on a different thread) may help in that case? I'm not 100% about the architecture of an XLL so I don't know if it allows threads like that or not (again, even if it has nothing to do with the host/Excel at all).

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

I would use a timer running in a different thread. Probably, this is an unnecessary precaution but I prefer to be safe.

Andrei Smolin
Add-in Express Team Leader