从VBA传递数组到VB.NET

Question

我正在开发一个vb.net COM互操作程序，以在Microsoft Excel中使用，并且我在将数组从vb传递到vb.net时遇到了问题。 在vb.net代码中，我有一个名为PointPairs的属性需要从vb设置，但是我无法传递二维数组。 我尝试过直接使用二维数组设置属性，也尝试了将两个一维数组传递给Sub来设置vb.net中的属性，但是我尝试的所有方法都似乎不起作用。

vb.net 代码:

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代码：

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)

一切都被vb.net正确地导出，属性/子程序/函数在vba的对象浏览器中可见，但是当我尝试在这两种方法中传递数组时，我会收到错误消息功能或接口标记为受限制，或该功能使用Visual Basic不支持的自动化类型或未定义的子程序或函数。我还尝试使用<MarshalAs()>，但我以前从未使用过它，并且找不到有关如何在vb和vb.net之间传递数组的文档。提前感谢任何建议或解决方案。

- Taylor Tvrdy

Answer 1

对于任何对解决方案感兴趣的人，我发现这篇文章正是我所需要的。

我必须在VBA中将2D数组拆分为两个1D Double数组，并将它们作为对象传递到vb.net中，并按照文章中的说明修改它们。我将SetPointPairs子程序更改如下，并在.net代码中添加了此私有函数以从Object转换为Array。

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

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

- Taylor Tvrdy