Add user defined function to Visual Studio Excel Add-in

Question

In visual studio I have an Excel 2010 Add-in project. How can I have that project create the following module:

I know I can save that workbook with that module then use it with my add in. It will be nice if I can have my add-in create that module...

Answer 1

It is possible to create the module. However for this to work the setting to "Trust access to the VB Project model" must be selected in Excel. It throws an error that access is denied if the trust setting is not selected.

using Excel = Microsoft.Office.Interop.Excel;
using VB = Microsoft.Vbe.Interop;

Excel.Application eApp = new Excel.Application();

eApp.Visible = true;
Excel.Workbook eBook = eApp.Workbooks.Add();

VB.VBProject eVBProj = (VB.VBProject)eBook.VBProject;
VB._VBComponent vbModule = eVBProj.VBE.ActiveVBProject.VBComponents.Add(VB.vbext_ComponentType.vbext_ct_StdModule);

String functionText = "Function MyTest()\n";
      functionText += "MsgBox \"Hello World\"\n";
      functionText += "End Function";

vbModule.CodeModule.AddFromString(functionText);

Answer 2

I dont think that VSTO supports Excel UDF's, the general recommendation is to use Automation Add-in's (as Sid's link suggests).

Another option is to call a managed VSTO function from VBA. Once again this is not recommended but possible.

(Recap of tutorial from link) Here is any easy way to call Managed functions from VBA.

Create a class with your functions in VSTO

<System.Runtime.InteropServices.ComVisible(True)> _
Public Class MyManagedFunctions
    Public Function GetNumber() As Integer
        Return 42
    End Function
End Class

Wire up your class to VBA in VSTO

Private Sub ThisWorkbook_Open() Handles Me.Open
    Me.Application.Run("RegisterCallback", New MyManagedFunctions)
End Sub

Create Hook for managed code and a wrapper for the functions in VBA

In a VBA module in your spreadsheet or document

Dim managedObject As Object

Public Sub RegisterCallback(callback As Object)
    Set managedObject = callback
End Sub

Public Function GetNumberFromVSTO() As Integer
    GetNumberFromVSTO = managedObject.GetNumber()
End Function

Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.

http://blogs.msdn.com/b/pstubbs/archive/2004/12/31/344964.aspx

Answer 3

If what you really want to do is to write .NET UDFs, or a combined .NET application level command and UDF addin then using VSTO is not currently a good solution:
I would recommend using either Addin Express (costs) or Excel DNA (free).
Both of these allow you to create both .NET XLL UDF addins and Automation UDF addins (XLL UDF addins offer significant performance advantages but with slightly more restricted access to the Excel object model)

Answer 4

A VSTO addin can't create UDF's, so you need to create a separate addin for the functions. Although this addin can be in the same DLL as the VSTO addin, you cannot communicate between the VSTO and the UDF's without special trickery.

I have a blog post about this. It gives you a complete example project that includes VSTO and UDF's.

Here is the basic structure of the UDF itself.

[Guid("3B81B6B7-3AF9-454F-AADF-FAF06E5A98F2")]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[ComVisible(true)]
public interface IFunctions
{
    int MYINT();
}

[Guid("F58C591D-A22F-49AD-BC21-A086097DC26B")]
[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
public class Functions : IFunctions 
{
    public int MYINT()
    {
        return 42;
    }
}