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

问题描述

我正在开发一个在 Microsoft Excel 中工作的 vb.net COM 互操作，我在将数组从 vb 传递到 vb.net 时遇到了问题.我在需要从 vb 设置的 vb.net 代码中有一个 PointPairs 属性，但在传递二维数组时遇到了问题.我已经尝试使用 2D 数组显式设置属性以及将两个 1D 数组传递到 Sub 以尝试在 vb.net 中设置属性，但我尝试过的任何方法似乎都不起作用.

vb.net 代码:

公共属性 PointPairs() As Double(,)得到...返回数组结束获取设置(ByVal 值(，)为双精度)...结束集结束属性Public Sub SetPointPairs(ByRef spline As Spline, ByRef xValues() As Double, _ByRef yValues() As Double)暗值(，)为双ReDim 值(1，UBound(xValues，1))For i As Integer = 0 To UBound(xValues, 1)值(0, i) = xValues(i)值(1, i) = yValues(i)下一个spline.PointPairs = 值结束子

vb代码:

将样条线 1 变暗为新样条线Dim points(), xValues(), yValues() As Double'从 excel 单元格中读取输入到 points() 数组中/将 x 和 y 值添加到相应的数组中spline1.PointPairs = points '第一种方法(不起作用)调用 SetPointPairs(spline1, xValues, yValues) '第二种方法(不起作用)

vb.net 正确导出了所有内容，并且属性/子程序/函数在 vba 的对象浏览器中可见，但是当我尝试以这两种方法传递数组时，我收到错误消息 函数或接口标记受限制，或者函数使用 Visual Basic 中不支持的自动化类型 或 Sub 或 Function not defined.我也尝试过使用 <MarshalAs()> 但我以前从未使用过它，也找不到太多关于如何使用它在 vb 和 vb.net 之间传递数组的文档.p>

提前感谢任何建议或解决方案

解决方案

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

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

我不得不在 VBA 中将 2D 数组分解为两个 Doubles 的 1D 数组，并将它们作为对象传递到 vb.net 中，并按照文章中的说明进行修改.我将 SetPointPairs Sub 更改如下，并添加了此 Private Function 以在 .net 代码中从 Object 转换为 Array

Sub SetPointPairs(ByRef spline As CubicSpline, ByRef xValues As Object, ByRef yValues As Object) 实现 iCubicSpline.SetPointPairs将 xDbls()、yDbls()、pointDbls(,) 调暗为 DoublexDbls = 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)下一个spline.PointPairs = pointDbls结束子私有函数 ComObjectToDoubleArray(ByVal comObject As Object) As Double()将 thisType 调暗为 Type = comObject.GetTypeDim dblType As Type = Type.GetType("System.Double[]")将 dblArray(0) 调暗为双精度如果 thisType 是 dblType 则Dim args(0) 作为对象Dim numEntries As Integer = CInt(thisType.InvokeMember("Length", BindingFlags.GetProperty, _什么都没有，comObject，什么都没有))ReDim dblArray(numEntries - 1)对于 j 作为整数 = 0 到 numEntries - 1参数(0)= jdblArray(j) = CDbl(thisType.InvokeMember("GetValue", BindingFlags.InvokeMethod, _什么都没有，comObject，args))下一个万一返回 dblArray结束功能

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:

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 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.

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 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屋！