excel - Optional Argument of COM Add-in vs Automation Add-in Written in C#

Question

I am working on a library of COM Add-in and Excel Automation Add-in, whose core codes are written in C#. I'd like to set an optional argument for the function and I know that this is legal for both C# and VBA, and even Excel WorksheetFunction. But I find that finally the optional argument works exclusively for COM and Automation add-in, meaning that if one add-in is run first, then works well but the optional argument of the other one will not work.

Below please see the example:

In the VS 2013 solution, I have two projects: one is called TestVBA and another one is called TestExcel.

TestVBA is for the COM add-in and built through the "Excel 2013 Add-in" and there are two .cs files:

1. ThisAddIn.cs

This file is generated automatically and modified a little bit. The codes are

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;

namespace TestVBA
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        private ExcelVBA oExcelVBA;

        protected override object RequestComAddInAutomationService()
        {
            if (oExcelVBA == null)
            {
                oExcelVBA = new ExcelVBA();
            }
            return oExcelVBA;
        }
        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

        #endregion
    }
}

2. TestVBA.cs

This file is the main calculation file of COM add-in. The codes are

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;


namespace TestVBA
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class ExcelVBA
    {
        public int TestAddVBA(int a = 1, int b = 1)
        {
            return a + b;
        }
    }
}

Another TestExcel is for the Excel Automation add-in and built through the C# "Class Library" and there are two .cs files either:

1. BaseUDF.cs

This file defines the decoration of two attributes. The codes are

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace BaseUDF
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public abstract class BaseUDF
    {
        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type)
        {
            // Add the "Programmable" registry key under CLSID.
            Registry.ClassesRoot.CreateSubKey(
              GetSubKeyName(type, "Programmable"));
            // Register the full path to mscoree.dll which makes Excel happier.
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(
              GetSubKeyName(type, "InprocServer32"), true);
            key.SetValue("",
              System.Environment.SystemDirectory + @"mscoree.dll",
              RegistryValueKind.String);
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {
            // Remove the "Programmable" registry key under CLSID.
            Registry.ClassesRoot.DeleteSubKey(
              GetSubKeyName(type, "Programmable"), false);
        }

        private static string GetSubKeyName(Type type,
          string subKeyName)
        {
            System.Text.StringBuilder s =
              new System.Text.StringBuilder();
            s.Append(@"CLSID{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}");
            s.Append(subKeyName);
            return s.ToString();
        }

        // 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();
        }
    }
}

2. TestExcel.cs

This file is the main calculation file of Excel Automation add-in. The codes are

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.Win32;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Extensibility;

namespace TestExcel
{
    [Guid("7127696E-AB87-427a-BC85-AB3CBA301CF3")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class TestExcel : BaseUDF.BaseUDF
    {
        public int TestAddExcel(int a = 1, int b = 1)
        {
            return a + b;
        }
    }
}

After building, the two add-ins have been registered in the system and in Excel we can use them successfully.

For the Automation add-in, we call them in the spreadsheet as =TestAddExcel(2,3) and =TestAddExcel() both of them work very well and give the right result 5 and 2. However, when I try to call the COM add-in via

Sub TestVBA_Click()

Dim addIn As COMAddIn
Dim TesthObj As Object

Set addIn = Application.COMAddIns("TestVBA")
Set TestObj = addIn.Object

Range("Output").Value2 = TestObj.TestAddVBA(2, 3)
Range("Output").Offset(1, 0).Value2 = TestObj.TestAddVBA()

End Sub

The first call with all arguments existing works well, but for the second one with arguments missing shows the error Type mismatch.

The interesting thing is, when I close the test excel file and open it again, this time I test the COM add-in first, still via the above VBA codes, both two calls work very well. Then when I test the two spreadsheet functions which used to work well, only the first one is good, the second one with arguments missing =TestAddExcel() fails with #VALUE!.

It would be very nice if someone can help with this strange issue.

Answer 1

I am not sure how you Referenced the class library without Registering for COM? I see now, you're using Late Binding. I didnt know you could do that (didn't think it would let you) and suspect that is the problem, it also matches the Type mismatch error.

Follow the second solution in my canonical answer here on the 3 methods to call .Net from Excel or VBA and make sure you Register for COM:

Click on the Build tab and check the check box that says “Register for COM Interop”. At this point you have an extra step if you are running on Windows Vista or higher. Visual Studio has to be run with administrator privileges to register for COM Interop. Save your project and exit Visual Studio. Then find Visual Studio in the Start menu and right click on it and choose “Run as Administrator”. Reopen your project in Visual Studio. Then choose “Build” to build the add-in.

---

Optionally if the above doesn't work, follow the third solution in my answer and reference the Automation Add-In and use Early Binding, I've tested this and it works perfectly:

Sub TestVBA1_Click()

Dim addIn As COMAddIn
Dim TesthObj As Object

Set addIn = Application.COMAddIns("TestVBA")
Set TestObj = addIn.Object

Debug.Print TestObj.TestAddVBA(2, 3)
Debug.Print TestObj.TestAddVBA()


Dim dotNetClass As TestExcel.TestExcel
Set dotNetClass = New TestExcel.TestExcel

Debug.Print dotNetClass.TestAddExcel(7, 3)
Debug.Print dotNetClass.TestAddExcel()

End Sub