Using .NET and C# for Excel addin functions

[Pink Pig]

(I posted this earlier to microsoft.public.dotnet.languages.csharp,
but the response that I got said that that was the wrong group, so I'm
trying another.)

I'm about to start a fairly complex job that requires updating a lot
of old 1-2-3 spreadsheets to Excel spreadsheets and creating a driver
in C# that will load them (or a subset of them) and execute them.

There is a table of addin functions in the old 1-2-3 code that I want
to replace with C# code to do the same thing (more or less). I have a
few questions that I need to have answered so that I don't go down the
wrong path.

1) I know how to load and execute Excel spreadsheets under C# control,
but to date I have only done this with Excel spreadsheets that contain
constants. The new spreadsheets will contain formulas that use addin
functions. Can I create the code for these addin functions in the
parent C# module and pass references to them to each of the Excel
spreadsheets that may use them?

2) Can I create addin functions with variable length argument lists?
(I know that old 1-2-3 doesn't support such functions in principle,
but I figured out how to get around that, so the existing 1-2-3 code
uses some addin functions with variable-length argument lists.)

3) In trying to modernize my approach to some of these issues, I
installed VSTO under Visual Studio 2005. This gives me some new
project types that I can create specifically for MS Office. I've
installed it in two different environments, one PC that has Office
2007 installed and another PC that has Office 2003 installed. I get
two slightly different versions of the default template. Is this
normal? What are the differences?

4) The template I got for the project type "Excel Workbook" with
Office 2003 (the closest project type that I found with Office 2007 is
called "Excel add-in" instead) is exceedingly stripped down. There is
a single class containing two functions, one called "...Startup" and
one called "...Shutdown". What I don't know (or understand) is how to
go about adding new functions to this template that actually do
something. Is there any online documentation that describes this? (I
haven't been able to track down anything useful yet, but that could be
explained by the fact that I don't know what I am looking for.)

5) I got the impression that if I wanted to implement a new addin
function for Excel, I would merely have to define the function
somewhere with the appropriate argument list, and everything else
would be automatically handled. Is this correct? If not, what else do
I have to do?

6) On the PC that I am sending this message from, I installed VS 2005
and VSTO almost 2 years ago. I also installed Office 2003 at the same
time, and I've never tried to upgrade it to Office 2007. When I invoke
File/New/Project on this machine and look in the Office folder, I get
5 choices, none of which is named "Excel add-in". On my office PC, I
installed VS 2005 and VSTO less than a month ago, and I installed
Office 2007 at the same time. When I invoke File/New/Project on my
office PC, I get many fewer choices, among which is "Excel add-in".
"Excel workbook" and "Excel add-in" look similar, in the sense that
they both have functions called "...Startup" and "...Shutdown", but I
can't tell for sure if that is the only difference. Is it? I did
notice that in the later (?) version, there is a region labelled "VSTO
generated code" in the Startup function, which is not present in the
earlier version. Why? Does this mean that I need to manually supply
the equivalent of the VSTO generated code if I use the old version?

7) [gripe]Why is this so difficult and confusing?[/gripe] You don't
have to answer this -- I will form my own conclusions.

8) It's been a while since the last time I tried to test this
interface, and at that time I only had the original (old) version to
work with. I tried to create an addin that implemented 2 functions,
but I could never get it to work. The available documentation only
describes a module that contains a single function. Either I am
overlooking something (possibly simple, but almost certainly
undocumented), or there is a bug or limitation in the older versions
(and of course possibly in the newer versions as well). Which is it? I
have more that 60 addin functions to supply, and I don't want to spend
a lot of time beating my head against the wall trying to shoehorn them
into a single module, if in fact it is impossible to do so.

 

[Cindy M.]

Hi Pink,

discussion in-line. I don't have answers to everything, but I hope this
will get you started in the right direction...

I'm about to start a fairly complex job that requires updating a lot
of old 1-2-3 spreadsheets to Excel spreadsheets and creating a driver
in C# that will load them (or a subset of them) and execute them.

There is a table of addin functions in the old 1-2-3 code that I want
to replace with C# code to do the same thing (more or less). I have a
few questions that I need to have answered so that I don't go down the
wrong path.

1) I know how to load and execute Excel spreadsheets under C# control,
but to date I have only done this with Excel spreadsheets that contain
constants. The new spreadsheets will contain formulas that use addin
functions. Can I create the code for these addin functions in the
parent C# module and pass references to them to each of the Excel
spreadsheets that may use them?

If the functions are in the worksheet cells (as opposed to a VBA module)
then Excel cannot access them as C# functions, directly, What you can do
is create a UDF class in your solution. See this blog entry, for example

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx

2) Can I create addin functions with variable length argument lists?
(I know that old 1-2-3 doesn't support such functions in principle,
but I figured out how to get around that, so the existing 1-2-3 code
uses some addin functions with variable-length argument lists.)

I suggest you ask about this in the excel.programming newsgroup. Don't
mention C#, just ask if/how a UDF can use variable-length argument lists
and give a short example of what you have in mind.

3) In trying to modernize my approach to some of these issues, I
installed VSTO under Visual Studio 2005. This gives me some new
project types that I can create specifically for MS Office. I've
installed it in two different environments, one PC that has Office
2007 installed and another PC that has Office 2003 installed. I get
two slightly different versions of the default template. Is this
normal? What are the differences?

What you get is going to depend on exactly what it is you installed. If
you installed the full VSTO 2005 package (as a standalone app or part of
Team Suite) then you'll get document-level customizations for Excel and
Word, 2003 only. If installed only VSTO 2005 SE then you'll get only
Add-in templates for numerous Office 2003 and 2007 applications. If you
installed both, you'll get the union of the two sets.

Document-level customizations for office 2007 are only available as part
of VSTO 2008.

FWIW you can't use VSTO to create UDF functions.

4) The template I got for the project type "Excel Workbook" with
Office 2003 (the closest project type that I found with Office 2007 is
called "Excel add-in" instead) is exceedingly stripped down. There is
a single class containing two functions, one called "...Startup" and
one called "...Shutdown". What I don't know (or understand) is how to
go about adding new functions to this template that actually do
something. Is there any online documentation that describes this? (I
haven't been able to track down anything useful yet, but that could be
explained by the fact that I don't know what I am looking for.)

Here's the starting point for the VSTO documentation on MSDN

http://msdn.microsoft.com/en-us/library/d2tx7z6d.aspx

And here's a link for the VSTO forum

http://social.msdn.microsoft.com/Forums/en-US/vsto/threads/?
prof=required&lc=1033&page=1

5) I got the impression that if I wanted to implement a new addin
function for Excel, I would merely have to define the function
somewhere with the appropriate argument list, and everything else
would be automatically handled. Is this correct? If not, what else do
I have to do?

I'm not sure I understand this question, but perhaps the blog entry will
resolve it...

6) On the PC that I am sending this message from, I installed VS 2005
and VSTO almost 2 years ago. I also installed Office 2003 at the same
time, and I've never tried to upgrade it to Office 2007. When I invoke
File/New/Project on this machine and look in the Office folder, I get
5 choices, none of which is named "Excel add-in". On my office PC, I
installed VS 2005 and VSTO less than a month ago, and I installed
Office 2007 at the same time. When I invoke File/New/Project on my
office PC, I get many fewer choices, among which is "Excel add-in".
"Excel workbook" and "Excel add-in" look similar, in the sense that
they both have functions called "...Startup" and "...Shutdown", but I
can't tell for sure if that is the only difference. Is it? I did
notice that in the later (?) version, there is a region labelled "VSTO
generated code" in the Startup function, which is not present in the
earlier version. Why? Does this mean that I need to manually supply
the equivalent of the VSTO generated code if I use the old version?

As outlined in a reply further up, what you get depends very much on
which versions of the applications are installed on the machine. What
you should NEVER try is a parallel installation of two versions of
Office: either 2003 OR 2007, never both.

From the sound of it, VSTO 2005 SE wasn't installed on the PC with fewer
items in the list.

The MSDN documentation for which I provided the link will go into a lot
more detail, but in brief:

1. an "Excel workbook" is an alternative to "VBA code behind"; it links
a workbook to a set of code. The tools you provide will be available
only to that workbook.

2. An "Add-in" is a set of tools that will be available to the entire
Excel application.

7) [gripe]Why is this so difficult and confusing?[/gripe] You don't
have to answer this -- I will form my own conclusions.

Mmmm. There are a number of factors in play:

- Excel and Word are very complex applications that have been developing
and extending the core code for some twenty years. Adding things on that
weren't even the germ of an idea in the eighties sometimes stretches the
envelope, so providing a developer interface can get complex

- They're COM applications, not .NET, so some things are going to feel
"odd"

- Not everything avaible to the "native" classic VB interface can easily
be "translated" to the .NET and C# world; somethings just aren't
available directly.

- What is exposed or made "simple" for .NET depends to a certain extent
on the available resources (time + money); the dev teams set priorities
and can't cover everything.

8) It's been a while since the last time I tried to test this
interface, and at that time I only had the original (old) version to
work with. I tried to create an addin that implemented 2 functions,
but I could never get it to work. The available documentation only
describes a module that contains a single function. Either I am
overlooking something (possibly simple, but almost certainly
undocumented), or there is a bug or limitation in the older versions
(and of course possibly in the newer versions as well). Which is it? I
have more that 60 addin functions to supply, and I don't want to spend
a lot of time beating my head against the wall trying to shoehorn them
into a single module, if in fact it is impossible to do so.

Impossible to answer this as I have no idea where you're starting from
in the scenario you describe. But I hope the links I've provided get you
straightened out.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail