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);