CODEWITHSUNDEEP
c#excelvba
sguo
sguo

Reputation: 142

Error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic

I have a C# DLL with a function (ZeorCurveDLL_Excel) that takes arrays as arguments. I try to call it in Excel VBA. But my VBA code won't compile and I keep getting this error at the line of calling this function. I can view this DLL function in VBA Object browser and it looks fine there (I mean every arguments is recognized in the Object browser). I searched the questions and didn't find directly related. Any helps are appreciated. I added a simple test function test_sum and it worked fine in VBA call.

My segment of C# code is here

        public int ZeroCurveDLL_Excel(int StartDay, int NumOfMonths, double[] MonthlyRate, int[] MonthlyTerm, double[] YearlyRate, int[] YearlyTerm, int[] DCDateSeq, double[] BootstrapRate)
        {
            long[] MonTerm = new long[MonthlyTerm.Length];
            long[] YrTerm = new long[YearlyTerm.Length];
            long[] DtSeq = new long[DCDateSeq.Length];

            int i;
            for (i = 0; i < MonthlyTerm.Length; i++) { MonTerm[i] = MonthlyTerm[i]; }
            for (i = 0; i < YearlyTerm.Length; i++) { YrTerm[i] = YearlyTerm[i]; }
            for (i = 0; i < DCDateSeq.Length; i++) { DtSeq[i] = DCDateSeq[i]; }

            long x = ZeroCurveDLL(StartDay, NumOfMonths, MonthlyRate, MonTerm, YearlyRate, YrTerm, DtSeq, BootstrapRate);
            return (int)x;
        }

        public double test_sum(double a, double b)
        {
            return a + b;
        }

My VBA code to call the functions are below

Sub test()
    Dim ws As Worksheet
    Dim startday As Date
    Dim numofmon As Integer
    Dim i As Integer, x As Long
    Dim arrMonRate() As Variant, arrMonTerm() As Variant
    Dim arrYrRate() As Variant, arrYrTerm() As Variant
    Dim arrDtSeq() As Variant, arrRate() As Variant
    Dim x1 As Double, x2 As Double, z As Double
    
    Dim spline As Interest_Rate_Curve.spline
    Set spline = New Interest_Rate_Curve.spline

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    With ws
        .Range("J3:J" & .Range("J3").End(xlDown).Row).ClearContents
        startday = .Cells(3, 1).Value2
        numofmon = .Cells(3, 2).Value
        arrMonTerm = .Range("C3:C" & .Range("C3").End(xlDown).Row).Value
        arrMonRate = .Range("D3:D" & .Range("D3").End(xlDown).Row).Value
        arrYrTerm = .Range("E3:E" & .Range("E3").End(xlDown).Row).Value
        arrYrRate = .Range("F3:F" & .Range("F3").End(xlDown).Row).Value
        arrDtSeq = .Range("G3:G" & .Range("G3").End(xlDown).Row).Value2
        arrRate = .Range("H3:H" & .Range("H3").End(xlDown).Row).Value
        
        x = spline.ZeroCurveDLL_Excel(startday, numofmon, arrMonRate, arrMonTerm, arrYrRate, arrYrTerm, arrDtSeq, arrRate)
        
        x1 = 15.6
        x2 = 56.9
        z = spline.test_sum(x1, x2)
        MsgBox (z)
    End With
End Sub

Here is a screenshot of how the function look like in VBA Object Browser. Here is a screenshot of how the function look like in VBA Object Browser

Upvotes: 0

Views: 271

Answers (1)

sguo
sguo

Reputation: 142

Thanks everyone for your help. I did more research and figured it out. Here are two key points:

  1. For any array arguments in the C# function, I need to add the key word "ref" before it in the function signature, such as public int test_array_sum(ref int[] arr1, ref int[] arr2). With this change, the compile error as in the thread title is gone. This may look a little odd because in C#, the arrays are passed by ref by default. But it did solve the compile error in VBA code.

  2. As several of you pointed out in the comments above, I need to make sure the variable type passing into the dll function matches the function signature interpreted by the VBA. For example, the int in C# corresponds to Long in VBA, I have to make sure the arr1() as a Long type in my VBA code before I call the function. With this taken care of, the code won't complain about type mismatch.

With the two changes, the dll functions can be called and work as expected. Hope this will be helpful to other folks experiencing similar issues.

Upvotes: 2

Related Questions