Hi

Is there any way to implement a user defined function for use in spreadsheet
cells using VSTO? Is it possible/neccesary to use a vba add-in to make a call
back into a managed VSTO app?

You can just create a Class Library in VS2005, reference it from the
Automation Add-Ins dialog (access this from the XLA Add-Ins dialog), and
then use the function directly from a cell. Neither VBA nor VSTO is
necessary. See "Creating A Function Library In VB.NET" at
http://www.cpearson.com/Excel/Creati...nctionLib.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Andrew Wiles" wrote in message
...
Hi

Is there any way to implement a user defined function for use in
spreadsheet
cells using VSTO? Is it possible/neccesary to use a vba add-in to make a
call
back into a managed VSTO app?

Chip

It's not so much a case of "don't need VSTO". We have a fairly substantial
app that is already built in VSTO and I would therefore prefer not to
introduce another layer unless there is no other way.

I gues that our app could consist of a combination of VAST and xla but I
need to make sure that any function calls from the vba add-in access the
correct application state within the VSTO app.



"Chip Pearson" wrote:

You can just create a Class Library in VS2005, reference it from the
Automation Add-Ins dialog (access this from the XLA Add-Ins dialog), and
then use the function directly from a cell. Neither VBA nor VSTO is
necessary. See "Creating A Function Library In VB.NET" at
http://www.cpearson.com/Excel/Creati...nctionLib.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Andrew Wiles" wrote in message
...
Hi

Is there any way to implement a user defined function for use in
spreadsheet
cells using VSTO? Is it possible/neccesary to use a vba add-in to make a
call
back into a managed VSTO app?

The official response from Microsoft technical support on this question is
that you cannot implement a UDF in a VSTO application. It is neccesary to
create a second add-in which uses a hidden worksheet or remoting for
communication.

"Andrew Wiles" wrote:

Chip

It's not so much a case of "don't need VSTO". We have a fairly substantial
app that is already built in VSTO and I would therefore prefer not to
introduce another layer unless there is no other way.

I gues that our app could consist of a combination of VAST and xla but I
need to make sure that any function calls from the vba add-in access the
correct application state within the VSTO app.



"Chip Pearson" wrote:

You can just create a Class Library in VS2005, reference it from the
Automation Add-Ins dialog (access this from the XLA Add-Ins dialog), and
then use the function directly from a cell. Neither VBA nor VSTO is
necessary. See "Creating A Function Library In VB.NET" at
http://www.cpearson.com/Excel/Creati...nctionLib.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Andrew Wiles" wrote in message
...
Hi

Is there any way to implement a user defined function for use in
spreadsheet
cells using VSTO? Is it possible/neccesary to use a vba add-in to make a
call
back into a managed VSTO app?

On 1 Nov, 12:06, Andrew Wiles
wrote:
The official response from Microsoft technical support on this question is
that you cannot implement a UDF in a VSTO application. It is neccesary to
create a second add-in which uses a hidden worksheet or remoting for
communication.



"Andrew Wiles" wrote:
Chip

It's not so much a case of "don't need VSTO". We have a fairly substantial
app that is already built in VSTO and I would therefore prefer not to
introduce another layer unless there is no other way.

I gues that our app could consist of a combination of VAST and xla but I
need to make sure that any function calls from the vba add-in access the
correct application state within the VSTO app.

"Chip Pearson" wrote:

You can just create a Class Library in VS2005, reference it from the
Automation Add-Ins dialog (access this from the XLA Add-Ins dialog), and
then use the function directly from a cell. Neither VBA nor VSTO is
necessary. See "Creating A Function Library In VB.NET" at
http://www.cpearson.com/Excel/Creati...nctionLib.aspx.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Andrew Wiles" wrote in message
...
Hi

Is there any way to implement a user defined function for use in
spreadsheet
cells using VSTO? Is it possible/neccesary to use a vba add-in to make a
call
back into a managed VSTO app?- Hide quoted text -

- Show quoted text -

It looks to me like a shimmed Automation add-in is what you want.
That will do UDFs, and can be automatically loaded by C# code in your
VSTO add-in along these lines:

private static void SetUpAutomationAddIn(Excel.Application
excelApp, string automationAddInProgId)
{
// Can't enumerate this Office collection with foreach.
See
// http://support.microsoft.com/default...b;en-us;328347

for (int i = 1; i <= excelApp.AddIns.Count; i++)
{
if (excelApp.AddIns[i].progID ==
automationAddInProgId)
{
if (excelApp.AddIns[i].Installed == false)
{
excelApp.AddIns[i].Installed = true;
}

// The Automation add-in is set up.
return;
}
}

// Add a workbook if there isn't already one there. Need
to do this
// to ensure that the AddIn manager is available before we
call AddIns.Add.
// See Microsoft Knowledge Base Article - 280290
if (excelApp.Workbooks.Count == 0)
{
excelApp.Workbooks.Add(Missing.Value);
}

AddIn addedAddIn =
excelApp.AddIns.Add(automationAddInProgId, true);
addedAddIn.Installed = true;
}