从VBA传递数组到VB.NET [英] Passing arrays from VBA to VB.NET

问题描述

我工作的一个vb.net COM互操作在Microsoft Excel工作，我有传递数组从VB到vb.net麻烦。我有一个 PointPairs 属性在vb.net code，我需要从VB的设置，我有过的2维数组的麻烦。我曾尝试都与一个二维数组明确地设置属性以及传递两个一维数组成子，试图设置在vb.net的财产，但没有我曾尝试似乎工作。

I am working on a vb.net COM interop to work in Microsoft Excel and I am having trouble passing arrays from vb to vb.net. I have a PointPairs property in the vb.net code that I need to set from vb and I am having trouble passing the 2 dimensional array. I have tried both setting the property explicitly with a 2D array as well as passing two 1D arrays into a Sub to try and set the property in vb.net, but nothing I have tried seems to work.

vb.net code：

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：

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)

一切是由vb.net正确导出和性能/潜艇/功能是在VBA对象浏览器中可见的，但是当我尝试在这两种方式传递数组我得到错误信息功能或接口市场上赢得为受限制，或该函数使用Visual Basic中不支持自动化类型或子或函数定义。的MarshalAs（）＆GT;我一直在使用＆LT也试过，但我从来没有使用过它，无法找到如何使用它传递VB之间阵列多文档vb.net。

Everything is being exported correctly by vb.net and the properties/subs/functions 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 have also tried using <MarshalAs()> but I have never used it before and can't find much documentation on how to use it for passing arrays between vb and vb.net.

预先感谢任何建议或解决方案。

Thanks in advance for any suggestions or solutions

推荐答案

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

For anyone interested in the solution, I found this article that was exactly what I needed.

<一个href=\"http://www.$c$cproject.com/Articles/12386/Sending-an-array-of-doubles-from-Excel-VBA-to-C-us?fid=247508&select=2478365&tid=2478365\" rel=\"nofollow\">http://www.$c$cproject.com/Articles/12386/Sending-an-array-of-doubles-from-Excel-VBA-to-C-us?fid=247508&select=2478365&tid=2478365

我不得不分手二维数组双打两维数组在VBA，并将它们传递到vb.net作为对象，并对其进行修改的文章中概述。我改变了SetPointPairs子如下，并添加这个私有函数从对象转换为数组中的.NET code

I had to break up the 2D array into two 1D arrays of Doubles in VBA and pass them into vb.net as objects and modify them as outlined in the article. I changed the SetPointPairs Sub as follows and added this Private Function to convert from Object to Array in the .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

这篇关于从VBA传递数组到VB.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！