Passing arrays from VBA to VB.NET

Question

I am working on interworking vb.net COM to work in Microsoft Excel, and I'm having problems transferring arrays from vb to vb.net. I have a PointPairs property in vb.net code that I need to set from vb, and I'm having trouble transferring a 2-dimensional array. I tried to set the property explicitly using a 2D array, and pass two 1D arrays to Sub to try to set the property in vb.net, but none of what I tried seems to work.

Vb.net Code:

 Public Property PointPairs() As Double(,) Get ... Return array End Get Set(ByVal Value(,) As Double) ... End Set End Property Public Sub SetPointPairs(ByRef spline As Spline, ByRef xValues() As Double, _ ByRef yValues() As Double) Dim Value(,) As Double ReDim Value(1, UBound(xValues, 1)) For i As Integer = 0 To UBound(xValues, 1) Value(0, i) = xValues(i) Value(1, i) = yValues(i) Next spline.PointPairs = Value End Sub 

vb code:

 Dim spline1 As New Spline Dim points(), xValues(), yValues() As Double 'read input from excel cells into points() array/add x and y values to respective arrays spline1.PointPairs = points 'first method (doesn't work) Call SetPointPairs(spline1, xValues, yValues) 'second method (doesn't work) 

Everything is correctly exported by vb.net, and the / subs / functions properties are visible in the object browser in vba, however, when I try to pass arrays in these two approaches, I get error messages Function or interfaces markes as restricted, or the function uses an automation type not supported in Visual Basic or Sub or Function not defined , I also tried using <MarshalAs()> , but I have never used it before and cannot find much documentation on how to use it to transfer arrays between vb and vb. net.

Thanks in advance for any suggestions or solutions.

Answer 1

For anyone interested in a solution, I found this article exactly in what I need.

http://www.codeproject.com/Articles/12386/Sending-an-array-of-doubles-from-Excel-VBA-to-C-us?fid=247508&select=2478365&tid=2478365

I had to split a 2D array into two 1D doubles arrays in VBA and pass them to vb.net as objects and modify them as described in the article. I modified SubPointPairs Sub as follows and added this private function to convert from Object to Array to .net code

 Sub SetPointPairs(ByRef spline As CubicSpline, ByRef xValues As Object, ByRef yValues As Object) Implements iCubicSpline.SetPointPairs Dim xDbls(), yDbls(), pointDbls(,) As Double xDbls = ComObjectToDoubleArray(xValues) yDbls = ComObjectToDoubleArray(yValues) ReDim pointDbls(1, UBound(xDbls, 1)) For i As Integer = 0 To UBound(pointDbls, 2) pointDbls(0, i) = xDbls(i) pointDbls(1, i) = yDbls(i) Next spline.PointPairs = pointDbls End Sub Private Function ComObjectToDoubleArray(ByVal comObject As Object) As Double() Dim thisType As Type = comObject.GetType Dim dblType As Type = Type.GetType("System.Double[]") Dim dblArray(0) As Double If thisType Is dblType Then Dim args(0) As Object Dim numEntries As Integer = CInt(thisType.InvokeMember("Length", BindingFlags.GetProperty, _ Nothing, comObject, Nothing)) ReDim dblArray(numEntries - 1) For j As Integer = 0 To numEntries - 1 args(0) = j dblArray(j) = CDbl(thisType.InvokeMember("GetValue", BindingFlags.InvokeMethod, _ Nothing, comObject, args)) Next End If Return dblArray End Function