On using native .NET System.Timer

[w2xldna]

Hi Govert,

First of all, thanks for your great work on ExcelDNA!

I started converting a large VBA project into ExcelDna based XLL
plugin after reviewing all the positive feedback. One of the goals I
have is to gradually re-implement some functionalities using .NET
instead of COM. For example, I'm trying to replace VBA
Application.onTime() with .Net System.Timers. However, I found
ExcelReference() seems not working in a procedure triggered by Timer,
no matter if it's declared as local or global.

The code I attached here shows the Timer does call the sub
OnTimedEvent() and pops up messagebox, while you don't see any updates
on the Sheet. Interestingly, If I click "Say Hello" from the default
menu to bring up "Helloooo" messagbox, and keep it there, you actually
start to see cells getting updated.

Do you mind telling me if anything wrong or missed in the code? Thank
you! I will have much complex code in OnTimedEvent(), which calls
other subs and updates sheets.



<DnaLibrary Name="ExcelDna Test AddIn">
<Project Language="VB">
<Reference Path="System.Windows.Forms.dll"/>
<![CDATA[
Imports System.Windows.Forms
Imports System.Timers

Public Module MyTestFunctions

<ExcelCommand(MenuText:="Say &Hello")> _
Sub SayIt()
MessageBox.Show("Hellooooo!")
End Sub

<ExcelCommand(MenuName:="&My Menu", MenuText:="Start
Timer")> _
Public Sub StartTimer()
Dim aTimer As New System.Timers.Timer()
AddHandler aTimer.Elapsed, AddressOf OnTimedEvent
' Set the Interval to 1 second.
aTimer.Interval = 1000
aTimer.Enabled = True
aTimer.AutoReset = True
aTimer.Start()
End Sub

'<ExcelCommand()> _
<ExcelFunction(IsMacroType:=true)> _
public Sub OnTimedEvent(source As Object, e As
ElapsedEventArgs)
static i as integer

'Dim r as New ExcelReference(3,3)
Dim r as New ExcelReference(0,1,0,1,"Sheet1") 'using
explicet sheet name
r.SetValue(i)

'Dim target as ExcelReference = New
ExcelReference(3,3) 'same issue with a global reference
'target.SetValue(i)

i = i + 1
if(i < 3) Then
MessageBox.Show(i)
End If

End Sub

End Module

]]>
</Project>
</DnaLibrary>

[Govert van Drimmelen]

Hi,

Dealing with timers in Excel is hard. If you want to provide data to
the Excel sheet, the right way to do it is to make an RTD Server. You
might make some progress using the xlcOnTime or Application.OnTime
built-in triggers. But trying to drive an Excel sheet via a timer will
most likely lead to tears.

With that warning, it is possible to fix your example so that it kind
of works. I paste another version below.

Here is your immediate problem:
You are using System.Timers.Timer which is a timer for use with worker
threads in a multithreaded environment, typically in a server
environment. You can read more in the remarks for this timer class
here: http://msdn.microsoft.com/en-us/library/system.timers.timer.aspx
and in this comparison of different timers in .NET:
http://msdn.microsoft.com/en-us/magazine/cc164015.aspx.

Excel is (mostly) a single-threaded environment so you need to take
care to run macros on the main Excel thread.
The easiest way to ensure this with a timer is to use the
System.Windows.Forms.Timer which will raise its events on the thread
that creates the timer. But just executing code on the right thread
will still not allow you to modify the worksheet with xlSet (which is
what ExcelReference.SetValue does). You need to be in a context where
Excel is ready to accept changes, for example in a macro that Excel is
running. Your timer event handler is not such a context. So to
initiate the macro that updates the sheet from the timer event, I use
the COM API to call Application.Run(...). But this call might fail if
Excel is 'busy' - e.g. if you press the mouse button on the sheet, or
start editing a cell.

------

The ExcelReference and XlCall types are helpers for the Excel C API
defined in the Excel SDK. If you are porting code from VBA or
somewhere that used the COM interfaces, you might like to use these
instead. The trick is to use the 'ExcelDnaUtil.Application' in your
code to get to the root Application object. From there you can use the
Workbook, Worksheet and Range classes as you would from VBA or through
COM. From your timer you'd still want to put all the interesting code
in a macro that is run from the timer event via Application.Run so
that you can do all the error handling in one place.

------

Anyway - this gives you some idea of the issues to consider.

-Govert

<DnaLibrary Name="ExcelDna Test AddIn">
<Project Language="VB">
<Reference Path="System.Windows.Forms.dll"/>
<![CDATA[
Imports System.Windows.Forms

Public Module MyTestFunctions
<ExcelCommand(MenuText:="Say &Hello")> _
Sub SayIt()
MessageBox.Show("Hellooooo!")
End Sub

<ExcelCommand(MenuName:="&My Menu", _

MenuText:="Start Timer")> _
Public Sub StartTimer()

Dim aTimer As New Timer()
AddHandler aTimer.Tick, AddressOf OnTimedEvent

' Set the Interval to 1 second.
aTimer.Interval = 1000
aTimer.Enabled = True

aTimer.Start()
End Sub

Public Sub OnTimedEvent(source As Object, e As EventArgs)
' All kind of errors might occur here...
Try
ExcelDnaUtil.Application.Run("UpdateCell")
Catch
'What to do now...?
End Try
End Sub

' Excel-DNA will register this as an Excel macro
Public Sub UpdateCell()

static i as integer
'Dim r as New ExcelReference(3,3)

Dim r as New ExcelReference(0,0,0,0,"Sheet1")

r.SetValue(i)
'Dim target as ExcelReference = New ExcelReference(3,3)

[Govert van Drimmelen]

Ah,I should add that following whatever pattern you have used in your
VBA code that uses Application.OnTime repeatedly, or using the C API
equivalent which is something like    XlCall.Excel(XlCall.xlcOnTime,
DateTime.Now.AddSeconds(3), "RingBell")might work better than a timer.
But then you'll still have the problems of Application.OnTime, like
the copy/cut selection disappears, and what to do if the OnTime
doesn't run, since you probable reset the timer in the OnTime handler.
RTD seems to be a better model for cases where you want to update a
sheet with new data from time to time, though RTD servers have their
own quirks - you still have to be careful of the threading issues and
the error handling when calling back to Excel. But they seem to
integrate much better into the calculation model and the interactive
use of Excel than just setting data into some cells from time to
time.
Regards,Govert

[w2xldna]

Hi Govert,

I'm so impressed by your prompt reply with these helpful details and
links, giving me extra confidence on using ExcelDna now. :D

The code you provided works great. And it seems it's easier and better
to stick with Excel Object model, in which case MoreSamples.dna just
shows the simple/perfect code on how to use Timer.

I planned to migrate most VBA/COM code that's not directly bound to
Excel sheets/ranges/cells into native .Net code, such as Timer,
Regular Expression, and other pure calculation logic. I don't see a
clear path and strong support on VBA from MS, although they clarified
that no plan to remove VBA from future Office version... Now, it
turned out Timer is not a good candidate on this list. :)

BTW, my application is not data driven to update certain cells but
needs a timer to repeatedly run a bunch of routines in the background,
and read/update some cells if necessary, that's why I didn't go for
RTD server.

Thanks again for your excellent work and support on all users!

Eric