The question is quite long, so I'll format with bullet points for easier discussion
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.
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!
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!
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
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 !
I got two methods working, one for early binding and one for late binding which is unsatisfactory because it means doubling up every method.
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