I have created a COM library in c# which I am consuming from VBA in Excel. My library has a property which returns an array of objects but when I try to access the elements of the array I get this compile error in VBA: "Wrong number of arguments or invalid property assignment".
// C# property
Foo[] FooArray { get { return _fooArray; } }
' Client VBA code
Dim obj as new Bar
Dim f as Foo
set f = obj.FooArray(0)
I tried returning an array of strings and saw the same error.
Dim f as Foo
set f = obj.FooArray(0)
Four mistakes here. FooArray is a property, not a function. The property doesn't take an argument. Set is incorrect, the property returns an array, not an object. The type for f is wrong, the property returns an array, not a single Foo. This ought to be closer:
Dim f As Foo()
f = obj.FooArray
I think the problem comes from the ambiguity in VB with the FooArray(0) syntax. The parentheses can represent arguments of a Function/Sub call or an array indexer.
The solution is to explicitly declare an array, e.g. fa() as follows:
Dim f As Foo
Dim fa() As Foo
Dim b As New Bar
fa = b.FooArray
Set f = fa(0)
