IntellSense in VBA and Arrays from/to .NET COM DLL

[Martin Brunner]

Hi there,

I have to use a C#-Library from Excel VBA.
This __works__ fine over COM (Reference to the .tlb, that is created by .NET, out of VBA).
But only __without__ [ClassInterface(ClassInterfaceType.AutoDual)] in C#1

__With__, for providing IntelliSense, I get - when compiling the VBA code:
--> __Compile Error__: Function or interface marked as restricted, or the function uses an
automation type not supported in Visual Basic
(but just for the methods that use SAFEARRAY (TestSum, ...) not for DATE (KW) - see .tlb below)

How can I get both? Full functionality AND IntelliSense.

any idea(s)/solution(s)?
TIA, Martin@Vienna@Austria@Europe

smallest code that shows my problem in detail:
VBA Code:

Private Function range2array(rng As Range) As Double()
Dim m, n, i, j As Long
Dim a() As Double
m = rng.Rows.Count - 1
n = rng.Columns.Count - 1
ReDim a(m, n)
For i = 0 To m
For j = 0 To n
a(i, j) = rng.Cells(i + 1, j + 1).Value
Next
Next
range2array = a
End Function
 
Public Function TestSum(rngArray As Range) As Double
Dim o As New WorkingTime.Class1
// COMPILE ERROR (on .TestSum):
TestSum = o.TestSum(range2array(rngArray))
End Function
 

C# Code:

using System;
using System.Runtime.InteropServices;
 
namespace WorkingTime
{
//  THE SINGLE DIFFERENCE:
//    [ClassInterface(ClassInterfaceType.AutoDual)]
public class Class1
{
public Class1(){}
 
public double TestSum(System.Double[,] arr)
{
double sum = 0.0;
for(int r=0;r<arr.GetLength(0);r++)
for(int c=0;c<arr.GetLength(1);c++)
sum += arr[r,c];
return sum;
}
}
}
 

..tlb __without__ [ClassInterface(ClassInterfaceType.AutoDual)]: full functionality no IntelliSense
// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: WorkingTime.tlb

[
uuid(3A9C4D8F-E805-3BFE-8FD8-0686A34BE8B2),
version(1.0)
]
library WorkingTime
{
// TLib :     // TLib : Common Language Runtime Library : {BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}
importlib("mscorlib.tlb");
// TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
importlib("stdole2.tlb");
 
// Forward declare all types defined in this typelib
interface _Class1;
 
[
uuid(ECA63A36-9C29-3BB6-8C23-1DB74F653CEE),
version(1.0),
custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
]
coclass Class1 {
[default] interface _Class1;
interface _Object;
};
 
[
odl,
uuid(4C3E9A8D-E900-3384-8160-CD308CFF555C),
hidden,
dual,
oleautomation,
custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
 
]
interface _Class1 : IDispatch {
};
};
 

..tlb __with__ [ClassInterface(ClassInterfaceType.AutoDual)]: compile error, but IntelliSense
// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: WorkingTime.tlb

[
uuid(3AE2692B-065C-37E6-B581-6A34B9913359),
version(1.0)
]
library WorkingTime
{
// TLib :     // TLib : Common Language Runtime Library : {BED....6186D}
importlib("mscorlib.tlb");
// TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
importlib("stdole2.tlb");
 
// Forward declare all types defined in this typelib
interface _Class1;
 
[
uuid(3E3FBD07-3144-3F66-9621-50B0AF53833B),
version(1.0),
custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
]
coclass Class1 {
[default] interface _Class1;
interface _Object;
};
 
[
odl,
uuid(4B2E15B2-F4A4-3E39-A8CC-DAEEA58E019F),
hidden,
dual,
nonextensible,
oleautomation,
custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
 
]
interface _Class1 : IDispatch {
[id(00000000), propget,
custom({54FC8F55-38DE-4703-9C4E-250351302B1C}, "1")]
HRESULT ToString([out, retval] BSTR* pRetVal);
[id(0x60020001)]
HRESULT Equals(
[in] VARIANT obj,
[out, retval] VARIANT_BOOL* pRetVal);
[id(0x60020002)]
HRESULT GetHashCode([out, retval] long* pRetVal);
[id(0x60020003)]
HRESULT GetType([out, retval] _Type** pRetVal);
[id(0x60020004)]
HRESULT TestSum(
[in] SAFEARRAY(double) arr,
[out, retval] double* pRetVal);
[id(0x60020005)]
HRESULT TestXimesDataSum(
[in] SAFEARRAY(double) arr,
[out, retval] double* pRetVal);
[id(0x60020006)]
HRESULT KW(
[in] DATE dateTime,
[out, retval] long* pRetVal);
};
};
 

-------------------------------------------------------------------------
to get my real email address replace 'see@signature' with 'brunner@ximes'