Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add user defined function to Visual Studio Excel Add-in

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

enter image description here

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...

like image 299
Tono Nam Avatar asked Dec 19 '12 15:12

Tono Nam


People also ask

How do I enable User Defined Function in Excel?

To enable UDFsUnder Excel Services Settings, click User-defined functions. On the Excel Services User-Defined Functions page, click Add User-Defined Function to open the Excel Services Add User-Defined Function Assembly page.

How do you install Excel add-in from Visual Studio?

In Visual Studio, choose Create a new project. Using the search box, enter add-in. Choose Excel Web Add-in, then select Next.

How do you add a function in VBA?

If you want Excel VBA to perform a task that returns a result, you can use a function. Place a function into a module (In the Visual Basic Editor, click Insert, Module). For example, the function with name Area.


1 Answers

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);
like image 180
Sorceri Avatar answered Sep 27 '22 20:09

Sorceri