title: Issues with creating a C# .NET User Defined Function
body: <p>I am investigating creating an Automation AddIn for Excel as an alternative to Excel DNA. Referencing the article by Adam Tibi (<a href="http://www.adamtibi.net/07-2012/using-c-sharp-net-user-defined-functions-udf-in-excel" rel="nofollow noreferrer">http://www.adamtibi.net/07-2012/using-c-sharp-net-user-defined-functions-udf-in-excel</a>), I was able to get a class library to work with some issues.  The largest issue is that the formula does work but does not show in the list of Excel formulas.  Additionally, I would like to have intellisense on the UDF parameters, and would also like to know if there is any way to capture the cell references of where the formula was sent from.</p>
  
  <p>In terms of setup, I have made selected the "Make Assembly COM Visible" and the "Register for COM interop".</p>
  
  <p>Code-wise, I have the following classes.</p>
  
  <pre><code>using System;
  using System.Runtime.InteropServices;
  using Microsoft.Win32;
  
  namespace AutoExcel
  {
      public abstract class UdfBase
      {
          [ComRegisterFunction]
          public static void ComRegisterFunction(Type type)
          {
              Registry.ClassesRoot.CreateSubKey(
                  GetClsIdSubKeyName(type, "Programmable"));
  
              // Solves an intermittent issue where Excel
              // reports that it cannot find mscoree.dll
              // Register the full path to mscoree.dll.
              var key = Registry.ClassesRoot.OpenSubKey(GetClsIdSubKeyName(type, "InprocServer32"), true);
              if (key == null)
              {
                  return;
              }
              key.SetValue("", $"{Environment.SystemDirectory}\\mscoree.dll", RegistryValueKind.String);
          }
  
          [ComUnregisterFunction]
          public static void ComUnregisterFunction(Type type)
          {
              // Adds the "Programmable" registry key under CLSID
              Registry.ClassesRoot.DeleteSubKey(GetClsIdSubKeyName(type, "Programmable"));
          }
  
          private static string GetClsIdSubKeyName(Type type, String subKeyName)
          {
              return $"CLSID\\{{{type.GUID.ToString().ToUpper()}}}\\{subKeyName}";
          }
  
          // Hiding these methods from Excel
          [ComVisible(false)]
          public override string ToString()
          {
              return base.ToString();
          }
  
          [ComVisible(false)]
          public override bool Equals(object obj)
          {
              return base.Equals(obj);
          }
  
          [ComVisible(false)]
          public override int GetHashCode()
          {
              return base.GetHashCode();
          }
      }
  }
  </code></pre>
  
  <p>and</p>
  
  <pre><code>using System.Runtime.InteropServices;
  
  namespace AVstoAddIn
  {
      [ClassInterface(ClassInterfaceType.AutoDual)]
      [Guid("3CA06A95-F1A6-4C36-9E56-501BDC7AFDCF")]
      public class UdfTest : UdfBase
      {
          public double AddNumbers(double a, double b)
          {
              return a + b;
          }
  
          public double SubtractNumbers(double a, double b)
          {
              return a - b;
          }
      }
  }
  </code></pre>
  
  <p>When I attempt to take a Excel VSTO addin and apply the same techniques, I can get it to build by doing everything the same except for selecting "Register for COM interop".</p>
  
  <p>My development environment is Windows 10, Excel for Office 365 64-bit, Visual Studio Professional 2017 Version 15.9.7. My Excel Addin will need to support 2010, 2013, 2016 2019, and Office 365.</p>
  
  <p>I would like to have the function appear in the list of functions and to also create a category for it to appear under, but do not know how.</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p><strong>Edit 1</strong></p>
  
  <p>I figured out how to determine the range that the UDF was called from. Interestingly enough, the application does not get preserved even when I attempt to keep it in a static class/variable/property. I was setting the application in the ThisAddIn.cs within the ThisAddIn_Startup method, but the application was null when I tried to access it within the COM Automation method.</p>
  
  <pre><code>using System.Runtime.InteropServices;
  using Excel = Microsoft.Office.Interop.Excel;
  
  namespace AVstoAddIn
  {
       public static class ExcelApplication
       {
          static Excel.Application application;
  
          public static Excel.Application XlApplication
          {
             get =&gt; application ?? (Excel.Application) Marshal.GetActiveObject("Excel.Application");
             set { application = Globals.ThisAddIn.Application ?? (Excel.Application) Marshal.GetActiveObject("Excel.Application"); }
          }
       }
  }
  </code></pre>
  
  <p>In the COM Automation class mentioned above, the references may be found as follows below.</p>
  
  <pre><code>public class UdfTest : UdfBase
  {
      public double AddNumbers(double a, double b)
      {
          Excel.Application excelApp = ExcelApplication.XlApplication;
          Excel.Range target = (Excel.Range)excelApp.get_Caller(Type.Missing);
          string cellAddress = target.get_Address(Missing.Value, Missing.Value, Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value);
  
          return a + b;
      }
  }
  </code></pre>
  
  <p>My questions at this point are as follows:
  1. How to get the UDF to show when the user starts typing.
  2. How to get intellisense working on the UDF parameters.
  3. How to preserve the application.</p>