Implement Excel cell function using VSTO

[Guest]

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 Pearson]

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
www.cpearson.com/Excel/CreatingNETFunctionLib.aspx .


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

 

[Guest]

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.

 

[Guest]

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.

 

[MikeI]

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.








- 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.aspx?scid=kb;en-us;328347

for (int i = 1; i <= excelApp.AddIns.Count; i++)
{
if (excelApp.AddIns.progID ==
automationAddInProgId)
{
if (excelApp.AddIns.Installed == false)
{
excelApp.AddIns.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;
}