S Meaden S Meaden - 4 months ago 12
C# Question

On Windows, with a C# authored COM server, can one return a SAFEARRAY both for early bound and late bound code?

The question is quite long, so I'll format with bullet points for easier discussion

Introduction




  1. I'm writing a C# COM Server.

  2. the COM server is for use in Excel VBA both in early binding and late binding modes.

  3. My stumbling block is how to return a SAFEARRAY of instantiated classes that works both in early and late binding mode; I get errors.

  4. I have done plenty of work on this (all day):


    • I have done some diagnostics and setup the debugger to shed light on the errors I get.

    • I have done some fairly exhaustive googling.

    • I found some workarounds which are less than satisfactory.

    • I am now genuinely stumped and looking for a COM Interop expert to help me get to a good solution please.




To set up the project type and project properties




  1. Create a new C# Class library project.

  2. I named mine LateBoundSafeArraysProblem, also I renamed the source file to be LateBoundSafeArraysProblem.cs.

  3. In AssemblyInfo.cs amend line 20 to ComVisible(true) , so visibility is universal (still needs public keywords).

  4. Set the Project Properties:


    • Set the build options, in Project Properties->Build->Output I check the 'Register for COM interop' checkbox.

    • Set the debug options to launch Excel and load an excel workbook client:


      • In Project Properties->Debug->Start Action and select radio button 'Start external problem' and enter path to Microsoft Excel which for me is 'C:\Program Files\Microsoft Office 15\root\office15\excel.exe'.

      • In Project Properties->Debug->Start Options enter the name of a client Excel macro enabled workbook, which for me C:\Temp\LateBoundSafeArraysProblemClient.xlsm. †





To create the COM server source code




  1. Style choices and decisions


    • I'm being a good COM citizen and dividing the interface definitions from the class definitions.


      • I'm using [ClassInterface(ClassInterfaceType.None)] and [ComDefaultInterface(typeof(<interface>))] attributes on the class to effect this clear division.


    • Because the client is Excel VBA we need to stick to Automation compatible types, hence SAFEARRAY


  2. The two C# classes/ com classes:


    • Apples is a simple state vessel for marshalling data back to client and carries no methods except getters and setters.

    • FruitCounter is a worker class which has a method enumerateApples() which is to return a SAFEARRAY of instances of Apples.




So the source code for the Apples interface and class are:

public interface IApples
{
string variety { get; set; }
int quantity { get; set; }
}

[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IApples))]
public class Apples : IApples
{
public string variety { get; set; }
public int quantity { get; set; }
}


The above code is uncontentious and works fine.

The source code for the FruitContainer interface and class are

public interface IFruitCounter
{
Apples[] enumerateApples();
}

[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(IFruitCounter))]
public class FruitCounter : IFruitCounter
{
public Apples[] enumerateApples()
{
List<Apples> applesList = new List<Apples>();

//* Add some apples - well, one in fact for the time being
Apples app = new Apples();
app.variety = "Braeburn";
app.quantity = 4;
applesList.Add(app);

// * finished adding apples want to convert to SAFEARRAY
return applesList.ToArray();
}
}


And this will work for early binding but not for late binding.


  1. Build project, one should have a dll and a tlb.


    • One output will be LateBoundSafeArraysProblem.dll

    • a type library will also be output, LateBoundSafeArraysProblem.tlb ‡




The Early Bound Excel VBA client code




  1. Open the workbook specified in the debug startup options (see above †)

  2. Add a basic standard module (not a class module).

  3. Goto to Tools->References and check box for the type library generated (see above ‡)

  4. Add the following code




Sub TestEarlyBound()
'Tools -> References to type library LateBoundSafeArraysProblem.tlb
Dim fc As LateBoundSafeArraysProblem.FruitCounter
Set fc = New LateBoundSafeArraysProblem.FruitCounter

Dim apples() As LateBoundSafeArraysProblem.apples
apples() = fc.enumerateApples()

Stop

End Sub


When execution reached Stop then one can inspect the array contents for a successful marshalling.
SUCCESS FOR EARLY BINDING!

The Late Bound Excel VBA client code




  1. I also use late bounding in Excel VBA to get myself out of deployment scrapes, also I can hot swap dlls i.e. install a new COM server without closing Excel (I ought to post that trick on SO).

  2. From (1) I'm going to use the same Excel VBA workbook as the platform for testing the late binding and change the declarations accordingly.

  3. In the same module add the following code


    Sub TestFruitLateBound0()

    Dim fc As Object 'LateBoundSafeArraysProblem.FruitCounter
    Set fc = CreateObject("LateBoundSafeArraysProblem.FruitCounter")

    Dim apples() As Object 'LateBoundSafeArraysProblem.apples
    apples() = fc.enumerateApples() '<==== Type Mismatch thrown

    Stop

    End Sub





Running this code throws a Type Mismatch (VB error 13) at the marked line. So the same COM server code does not work in Excel VBA late binding mode.
FAILURE FOR LATE BINDING!

Workarounds




  1. So during investigation I wrote a second method with return type Object[], initially this didn't work because the generated idl dropped as asterisk. The idl went from


    // Return type Apples[] works for early binding but not late binding
    // works
    HRESULT enumerateApples([out, retval] SAFEARRAY(IApples*)* pRetVal);


    to


    // Return type Object[] fails because we drop a level of indirection
    // (perhaps confusion between value and reference types)
    // does NOT work AT ALL (late or early)
    HRESULT enumerateApplesLateBound([out, retval] SAFEARRAY(VARIANT)* pRetVal); // dropped as asterisk becomes SAFEARRAY to value types, no good

  2. Using the MarshalAs attribute fixed the number of asterisks


    // Still with Object[] but using MarshalAs
    // [return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = System.Runtime.InteropServices.VarEnum.VT_UNKNOWN)]
    // works for late-bound but not early bound !!! Aaaargh !!!!
    HRESULT enumerateApplesLateBound([out, retval] SAFEARRAY(IDispatch*)* pRetVal);


    and that worked for late binding but NOT for earling binding! Aaaargh !



Summary



I got two methods working, one for early binding and one for late binding which is unsatisfactory because it means doubling up every method.

How to get one method working for both early and late binding please?


Answer

I did a bit of testing on this by marshaling the returned value in to a Variant, and then dumped the memory of the returned VARIANT structure to see what the VARTYPE was. For the early bound call, it was returning a Variant with a VARTYPE of VT_ARRAY & VT_DISPATCH. For the late bound call, it was returning a VARTYPE of VT_ARRAY & VT_UNKNOWN. Apples should already be defined as implementing IDispatch in the tlb, but for some reason that eludes me, VBA is having difficulty handling an array of IUnknown from the late bound call. The work-around is to change the return type to object[] on the C# side...

public object[] enumerateApples()
{
    List<object> applesList = new List<object>();

    //* Add some apples - well, one in fact for the time being 
    Apples app = new Apples();
    app.variety = "Braeburn";
    app.quantity = 4;
    applesList.Add(app);

    // * finished adding apples want to convert to SAFEARRAY 
    return applesList.ToArray();
}

...and pull them into a Variant on the VBA side:

Sub TestEarlyBound()
    'Tools -> References to type library LateBoundSafeArraysProblem.tlb
    Dim fc As LateBoundSafeArraysProblem.FruitCounter
    Set fc = New LateBoundSafeArraysProblem.FruitCounter

    Dim apples As Variant
    apples = fc.enumerateApples()

    Debug.Print apples(0).variety   'prints "Braeburn"
End Sub

Sub TestFruitLateBound0()
    Dim fc As Object
    Set fc = CreateObject("LateBoundSafeArraysProblem.FruitCounter")

    Dim apples As Variant
    apples = fc.enumerateApples()

    Debug.Print apples(0).variety   'prints "Braeburn"
End Sub