Excel 2007 VSTO Addin UDF

Excel 2007 VSTO Addin UDF

• Josh Gammon

  Fixed, some typos
  
  

• Mr. Mike B

  that looks awesome. thx
  

• Mark Daly

  Hi Toby

  Please re-read the entire thread, paying special attention to Misha's reply: it states explicitly that this is not supported.

  
  
  

• bwvick

  Cool! Looks like a C# wrapper could be written to do the VBA code generation behind the scenes as well.

  Thanks,

  Andrew

  
  

• Anoop. H

  It does help, but we want a global set of functions that the workbook has no notion of. With the answer above you must put that CallbackReg code in every workbook that wants to use your functions.

  
  

• dotnetat43

  Is there any way to add UDFs to Word 2007 We're developing an Add-In for Word 2007 that uses formulas in fields and it would be very handy to be able to input our own UDFs for this purpose.
  
  

• Necromancer

  Hi Dan,

  Have a look at ExcelDna http://exceldna.typepad.com. It is an open-source library that allows you to create .xll addins using .Net. In particular, creating user-defined functions in .Net is easy, something not directly supported by VSTO.

  The user code can either be C# or VB code in text-based script files, or managed compiled .dlls. Also supported are custom categories, function and argument descriptions and the like. ExcelDna needs only the .Net 2.0 framework, and works with all versions of Excel (from Excel 97).

  Regards,

  Govert van Drimmelen

  
  

• Axel Schick

  I agree a more general mechanism is needed for UDFs to be useful from Excel Add-Ins. Any workarounds For example, can this VB macro be added programmatically to the current active workbook from the C# Add-In itself

  Thanks,

  Andrew

  
  

• OSIGeoff

  I have Microsoft Visual Studio Tools for Office 77617-168-7076001-41140 and Excel 2003 pro
  
  I'm trying to add UDF's to Excel. The Run funktion I have needs alot more arguments then the 2 you write.
  
  I have the code in ThisWorkbook.cs:
  
  private void ThisWorkbook_Startup(object sender, System.EventArgs e)
  {
  .
  .
  .
  Microsoft.Vbe.Interop.VBComponent component =
  thisWorkbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
  
  component.CodeModule.AddFromString(
  "Public MyUdf As Object\n"
  + "Public Sub CallbackReg(callback As Object)\n"
  + "\tSet MyUdf = callback\n"
  + "End Sub\n\n"
  + "Public Function GenRand() As int\n"
  + "\tGenRand = MyUdf.GenRand()\n"
  + "End Function");
  Globals.ThisWorkbook.ThisApplication.Run(("CallbackReg", new MyUdf(),
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  
  }
  
  And I have the class:
  
  [System.Runtime.InteropServices.ComVisible(true)]
  class MyUdf
  {
  public object GenRand()
  {
  return (new Random()).Next(100, 1000);
  }
  }
  
  But then I try to load Excel I get:
  The customization assembly could not ne found or could not be loaded.
  You can still edit and save the document.....
  
  Details:
  Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
  
  
  ************** Exception Text **************
  System.Runtime.InteropServices.COMException (0x80020005): Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
  at Microsoft.Office.Interop.Excel._Application.Run(Object Macro, Object Arg1, Object Arg2, Object Arg3, Object Arg4, Object Arg5, Object Arg6, Object Arg7, Object Arg8, Object Arg9, Object Arg10, Object Arg11, Object Arg12, Object Arg13, Object Arg14, Object Arg15, Object Arg16, Object Arg17, Object Arg18, Object Arg19, Object Arg20, Object Arg21, Object Arg22, Object Arg23, Object Arg24, Object Arg25, Object Arg26, Object Arg27, Object Arg28, Object Arg29, Object Arg30)
  at ExcelWorkbook4.ThisWorkbook.ThisWorkbook_Startup(Object sender, EventArgs e) in C:\projects\ExcelWorkbook4\ExcelWorkbook4\ThisWorkbook.cs:line 42
  at Microsoft.Office.Tools.Excel.Workbook.OnStartup()
  at ExcelWorkbook4.ThisWorkbook.FinishInitialization() in C:\projects\ExcelWorkbook4\ExcelWorkbook4\ThisWorkbook.Designer.cs:line 66
  at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecutePhase(String methodName)
  at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecuteCustomizationStartupCode()
  at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecuteCustomization(IHostServiceProvider serviceProvider)
  
  
  ************** Loaded Assemblies **************
  
  

• learn32.dll

  This is extremely important functionality to add especially with the click once type of manifest deployment the new VSTO addins appear to have. I work on a trading floor with 500 some computers. Each computer uses excel and some custom add-ins we wrote.

  If there is a bug in an addin you can see the pain.

  
  

• portlond

  I hear you and thanks for your feedback.
  
  

• Brian_369

  This functionality is not currently supported. We are looking at adding support for UDFs for Excel add-ins, but nothing is currently set in stone.
  
  

• Infodine

  Hi Dan,

  First you need to create a class (must be ComVisible) with your methods. E.g.:

  [System.Runtime.InteropServices.ComVisible (true)]

  public class MyUdf
  {
  public object MyFunction()
  {
  return "My Function!";
  }
  }

  Then put the following line in ThisWorkbook_Startup method:

  ThisApplication.Run ("CallbackReg", new MyUdf()); // this will start "CallbackReg" macro in your workbook

  Next you need to open your workbook and add new module with following code:
  
  Public MyUdf As Object

  Public Sub CallbackReg(callback As Object)
  Set MyUdf = callback
  End Sub

  Public Function MyFunction() As String
  MyFunction = MyUdf.MyFunction()
  End Function
  
  And this is it!

  Also, your document needs to be digitally signed or trusted in order to run CallbackReg macro.

  Hope this helps!

  
  
  

• JohnWilliams

  Hi Andrew,

  You can create Excel VSTO Add-in and programmatically insert the VBA code in every new workbook that gets opened.

  e.g.

  Microsoft.Vbe.Interop.VBComponent component = Wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

  component.CodeModule.AddFromString("..... add VBA code for UDFs ....");

  and finally call

  Globals.ThisApplication.Run("CallbackReg", new MyUdf());

  
  
  
• Excel 2007 VSTO Addin UDF