C# Written VBA Functions (COM Add-in) with Optional Argument

Question

I am working on some VBA COM Add-in and Excel 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 only Excel functions work well but VBA function always says type mismatch after the library has been registered in both COM Add-in and Excel Add-in.

Here is a simple example: in C# we have some function called

double NormalCDF(double x, double mu = 0, double sigma = 1);

In Excel spreadsheet, I can successfully call

NormalCDF(1.2, 2, 3)

or

NormalCDF(1.2)

Both give the right results. But in VBA, the following is successful,

TestObj.NormalCDF(1.2, 2, 3)

is good, while

TestObj.NormalCDF(1.2)

is failed with "Type mismatch".

Could anyone help with this problem?

//////////////////////////////////////////////////////////////////////////// 08/10/2018 Update

Please see a simplified example code: In "MyLibraryExcel.cs" I have

...
public interface IWorksheetFunctions
{
    int test(int a = 1, int b = 1);
}
...
public class WorksheetFunctions : MoodysMathUdfBase,IWorksheetFunctions, IDTExtensibility2
{
    protected Application ExcelApplication { get; set; }
    public int test(int a = 1, int b = 1)
    {
        return a + b;
    }
}

In "MyLibraryVBA.cs" I have

...
public interface IExcelVBA
{
    int test(int a = 1, int b = 1);
}
...
public class ExcelVBA : IExcelVBA
{
    public int test(int a = 1, int b = 1)
    {
        return a + b;
    }
}

After building the projects, both two libraries have been registered. In Excel spreadsheet, I want to call

=test(2,3)

which is expected to return 5, and call

=test()

which is expected to return 2.

In VBA macro, I have some codes

Sub TestVBA()
range("Output1").value=TestObj.test(2,3)
range("Output2").value=TestObj.test()
End Sub

which is expected to get 5 and 2 as well.

The interesting thing is that, if I run the two functions of Excel spreadsheet (click the cells and press Enter), both work fine, then run the two functions of VBA, only the first one works, the second one is failed with "Type Mismatch". However, if I run the VBA first, both two test() work fine, then run the two functions of Excel spreadsheet, only the first one works, the second one will display #Value.

Answer 1

Could you please post your VBA Code?

I have tried with below code and it is working fine

Sub OptionalArgs(firstValue As Double, Optional secindValue As Double = 0)

     MsgBox CStr(firstValue)

End Sub

Answer 2

try this:

object missing = System.Reflection.Missing.Value;

 TestObj.NormalCDF(1.2, missing , missing);