Arrays: Defined in .NET, Applied in VBA

Question

I have created a VB.NET 2008 Class Library that has, for the most part, been working quite well with VBA. I can perform most of the functionality that is required but I have recently run into a pretty severe problem.

The reason we are working with both programming interfaces is because we need to be able to do "on the fly" coding and that can only be accomplished in VBA. So, as I mentioned, there is "backbone" code in .NET. The .NET code contains arrays, fields, properties, etc. (your typical program structure). My problem is the arrays.

Alright, in the .NET DLL I have an array - let's call it X() as Double. There are two properties since VBA doesn't support overloading. One is: X(rowNum) as Double, and the other is: X() as Double(). Notice the first property returns only one element of the array while the latter returns the entire array.

In my VBA program I can read a file and fill this array with values (this is all in the DLL). However I can't manipulate those same values once inside VBA.

For instance, in VBA I cannot say X(5) = 10. It won't change the value. When I try setting the entire X() array to another I get the Compile Error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic.

Answer 1

Instead of manipulating the array directly with eg X(5) = 10, you might have more luck creating get/set type methods. Check here for some examples.

Public Sub SetX (ByVal i As Integer, ByVal v As Integer)
    X(i) = v
End Sub

Public Function GetX (ByVal i As Integer) As Integer
    Return X(i)
End Function