Capabilities of Excel-DNA

[Jon49]

So I've been reading about Excel-DNA for the last couple of days and
trying to understand how it would be useful in my application.

So can you use Excel-DNA to capture events in Excel and then respond
to those events? If not can I use VB.NET Express to capture these
events? Can I use Express to hold data and then use Excel-DNA for
formulas and access the data from my data that is in memory and not in
the excel spreadsheet?

If I can't capture events in either of these programs can I do it with
any other free program other than VBA that I would be able to
integrate everything? Do I just need to purchase a program?

Thanks for your help in understanding the capabilities of Excel-DNA!

[Govert van Drimmelen]

Hi,

You need no special development environment to enable any of the Excel-
DNA features. One you have Excel-DNA and the .NET runtime installed,
it can all be done in our favourite text editor, or you can use a free
IDE like one of the Visual Studio Express editions or SharpDevelop
(http://www.sharpdevelop.net/OpenSource/SD/Default.aspx). Ross McLean
wrote a very nice series of blogposts on getting started with Excel-
DNA, including how to use SharpDevelop:
http://www.blog.methodsinexcel.co.uk/2010/09/22/writing-an-excel-dna-function-using-an-ide/.

Dealing with the Excel events is possible with Excel-DNA. There are
two approaches:
1. Using the C API (XlCall class in Excel-DNA). You only have a small
number of events, but they should work on all versions of Excel with
no other dependencies. To handle these you create a macro in Excel-
DNA, and then call XlCall.OnXXXX, "MyMacro" to set up the handler.
The events available through the C API are:
xlcOnDoubleClick
xlcOnEntry - user entered data into a cell
xlcOnKey - user pressed a keystroke
xlcOnRecalc
xlcOnSheet
xlcOnWindow - worksheet switch
xlcOnTime - scheduled macro

2. Using the COM API. This way you can access all the events you know
from VBA, but need to learn about "Primary Interop Assemblies", and
will have to take some care dealing with different versions of Excel.
I think some other Excel-DNA users would know more about how to
redistribute the required assemblies and deal with different Excel
versions.
(In theory it is also possible to deal with the COM events in a late-
bound way, without being tied to a specific Excel version or having to
redistribute the version-specific PIA assemblies. But there is no
built-in support for this in Excel-DNA at present, and it would take
quite a bit of work to make something that works safely for the
various Excel versions.)

The example below exhibits both ways of event handling, and works on
my machine with Excel 2007.
For the example to work on your side, you probably need to install the
PIA assemblies, which can be found here:
Excel 2003 - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&DisplayLang=en
Excel 2007 - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=59DAEBAA-BED4-4282-A28C-B864D8BFA513

To run the example, create a text file (called EventTest.dna) with
everything from the <DnaLibrary> and </DnaLibrary> tags. Then copy the
file ExcelDna.xll from the Excel-DNA Distribution directory and call
it EventTest.xll, and File->Open it in Excel.
(Of course this could all be in a compiled library too.)

If you have more specific questions, I'm happy to make an example to
help you get started.

Regards,
Govert

<DnaLibrary Name="Excel-DNA Event Test">
<Reference Name="System.Windows.Forms" />
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[

Imports SWF = System.Windows.Forms
Imports Microsoft.Office.Interop.Excel
Imports ExcelDna.Integration

Public Class MyAddIn
Implements IExcelAddIn

Dim WithEvents App As Application

Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
SWF.MessageBox.Show("Opening the Excel-DNA Event Test add-in")

' Set the local App variable,
' which implicly hooks up the SheetActive handler
App = ExcelDnaUtil.Application

' Explicitly add a handler for the
' Application's SheetBeforeDoubleClick event
AddHandler App.SheetBeforeDoubleClick, AddressOf
MyDoubleClickHandler
End Sub

Public Sub AutoClose() Implements IExcelAddIn.AutoClose

End Sub

Private Sub Application_SheetActivate(ByVal sh As Object) _
Handles App.SheetActivate

System.Windows.Forms.MessageBox.Show("SheetActivate Handled: "
+ sh.Name)
End Sub

Private Sub MyDoubleClickHandler(ByVal sheet As Object, _
ByVal target As Range, _
ByRef cancel As Boolean)
Dim message As String = String.Format( _
"Sheet {0} double-clicked at {1}.", sheet.Name,
target.Address)
SWF.MessageBox.Show(message)
End Sub

End Class

Public Module TestEvents
<ExcelCommand(MenuName:="Event Test", MenuText:="Install Calculate
Handler")> _
Sub InstallCalculateHandler()
XlCall.Excel(XlCall.xlcOnRecalc, ExcelMissing.Value,
"MyOnRecalcHandler")
End Sub

<ExcelCommand(MenuName:="Event Test", MenuText:="Remove Calculate
Handler")> _
Sub UninstallCalculateHandler()
XlCall.Excel(XlCall.xlcOnRecalc)
End Sub

Sub MyOnRecalcHandler()
XlCall.Excel(XlCall.xlcAlert, "OnRecalc event received ...")
End Sub

End Module
]]>
</DnaLibrary>

[Jon49]

Thanks Govert,

I actually read through some of that material and it was very helpful
it just didn't let me know if I could use it as a full blown add-in or
just use it formulas. I guess you're saying I can use it as a full
blown add-in.

I was reading and it seemed I could use VB.NET to capture events in
Excel (I haven't tested that yet) so if I did it that method would it
still work in through your interface? Would I have to use the API
calls? I have a book that let's me know some of the problems that are
encountered with API calls, so I should be able to handle it,
hopefully.

I would need WorkbookBeforeSave, WorkbookBeforeClose,
SheetFollowHyperlink. Do you think those are doable?

Monday I'll start actually playing with it more. So far I've just been
reading through examples, I just didn't want to jump in all the way
until I know I'll be able to use it for my application.

Thanks so much for the detailed reply, I'll definitely go through
those examples and I'll check out the websites.

On Feb 26, 3:18 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> You need no special development environment to enable any of the Excel-
> DNA features. One you have Excel-DNA and the .NET runtime installed,
> it can all be done in our favourite text editor, or you can use a free
> IDE like one of the Visual Studio Express editions or SharpDevelop
> (http://www.sharpdevelop.net/OpenSource/SD/Default.aspx). Ross McLean
> wrote a very nice series of blogposts on getting started with Excel-

> DNA, including how to use SharpDevelop:http://www.blog.methodsinexcel.co.uk/2010/09/22/writing-an-excel-dna-....

> Excel 2003 -http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3c9a983a-...
> Excel 2007 -http://www.microsoft.com/downloads/en/details.aspx?FamilyID=59DAEBAA-...

[Govert van Drimmelen]

Hi,

So one point of my example is to show that you can use VBA.NET to talk
to the COM interface and handle those events from within an Excel-DNA
add-in.

The issue with the event handling is deciding how to manage the
interop assemblies, because they are specific to the Excel version and
they are a hassle to distribute and install if you want a small self-
contained add-in. If your add-in really only needs to handle three
events, it's probably worth figuring out how to do it late-bound. (My
starting point would be this article: http://www.codeproject.com/KB/cs/zetalatebindingcomevents.aspx.)
The right time to look at this would be after you have everything
working on one machine, and want to start thinking about deploying to
many others.

-Govert

[Govert van Drimmelen]

Oh, and if you are able to target .NET 4, you might be able to use the
PIA embedding feature: http://msdn.microsoft.com/en-us/library/ee317478.aspx

I have not tried it but I guess it's time to have a look.
(Has anyone else tried it in an Excel-DNA add-in?)

-Govert

[Jon49]

I implemented your code (in VB.NET 2010) and went over the tutorial
and it works great. I used the method number 4 for implementing
backwards compatibility see http://devcity.net/Articles/163/1/Articles.aspx
.

The debugging works great, I can see the workbook when I debug (at
least for what you gave me, I haven't tested everything yet).

The only question I had was what the module "Public Module TestEvents"
was supposed to do. I tried looking up ExcelCommand but couldn't find
anything that would tell me exactly what it's purpose was (on just a
cursory look). Is there a place that documents the purpose of all the
different functions you provide besides the source code?

Thank you so much for creating this project for free. When I finish my
project and start making money from it (hopefully) I'll have to pass
some of it your way!

This is the code I used in .NET, it worked wonderfully:

Imports ExcelDna.Integration
Imports My.Excel03P.Interop

Public Class AppEventsHandler

Implements IExcelAddIn
Private WithEvents XLApp As Application

Public Sub Start() Implements IExcelAddIn.AutoOpen
XLApp = ExcelDnaUtil.Application
AddHandler XLApp.SheetBeforeDoubleClick, AddressOf
MyDoubleClickHandler
End Sub

Public Sub Close() Implements IExcelAddIn.AutoClose
'Fires when addin is removed from the addins list
'but not when excel closes - this is to
'avoid issues caused by the Excel option to cancel
' out of the close 'after the event has fired.
msgbox("Bye bye, from DNA")
End Sub

Private Sub Application_SheetActivate(ByVal sh As Object) Handles
XLApp.SheetActivate

MessageBox.Show("SheetActivate Handled: " + sh.Name)
End Sub

Private Sub MyDoubleClickHandler(ByVal sheet As Object, ByVal
target As Range, ByRef cancel As Boolean)
Dim message As String = String.Format( "Sheet {0} double-

clicked at {1}.", sheet.Name, target.Address)

MessageBox.Show(message)
End Sub

[Govert van Drimmelen]

Hi,

The Module TestEvents shows how to take the first approach I
mentioned, which is to use the native (XlCall) API to hook up events.
There are two macros defined which respectively hook up and unhook a
listener to the Recalc event.

The <ExcelCommand> attribute is an Excel-DNA attribute that allows you
to easily make menu entries for the macros.
ExcelCommand is a counterpart to the ExcelFunction attribute, which
controls settings for user-defined functions.

If you are happy using the COM interface for your event handling, you
can ignore that for now. One reason to prefer the native (XlCall) API
is if you wanted to get the best performance when populating a sheet
with data. But it is certainly more tricky to use, and as I mentioned
you have access to a much smaller set of events.

-Govert

On Mar 4, 3:23 am, Jon49 <nyman...@gmail.com> wrote:
> I implemented your code (in VB.NET 2010) and went over the tutorial
> and it works great. I used the method number 4 for implementing

> backwards compatibility seehttp://devcity.net/Articles/163/1/Articles.aspx

[Jon49]

Experimenting some more I found, using the method I did, all I need is
the XLApp=ExcelDnaUtil.Application and I still have the full access to
all events. Pretty nice.

[Jon49]

Thanks Govert. I'll probably do a mix of both for ease of use and for
parts that need more speed I'll use the DNA way. Thanks for the
explanations, they are very helpful. I just looked at all the
documentation you created and that is also very helpful. I used my
code and put it in a packed xll and sent it over to the wife's
computer with XL2007 (I have 2003) and it worked splendidly.

It would definitely be nice to have a GUI to do many of these things.
Maybe that will be my contribution to the project, I won't guarantee
that I will do it though. But maybe I'll find some time to do it.
Would that be helpful to the project?