有关模块、类模块、DLL的数组传递

【代码示例1】

1，开始一个新的标准 EXE 项目。默认情况下创建 Form1。

2，将类模块添加到项目中。

3，将以下代码放在类模块中：

Option Explicit

Public Function ArrayFromClass() As String()
    Dim astr(1 To 10) As String
    Dim i As Integer
    For i = 1 To 10
        astr(i) = "Class array element " & Str(i)
    Next i
    ArrayFromClass = astr()
End Function

Public Function ArrayInt() As Variant
    Dim aint(1 To 5) As Variant
    Dim i As Integer
    For i = 1 To 5
        aint(i) = i
    Next i
    ArrayInt = aint()
End Function

4，将三个 CommandButtons 放在 form1 上。

5，向 form1 中添加以下代码：

Option Explicit

Private aiLeftSide() As Integer
Private asLeftSide() As String
Private aiRightSide(1 To 10) As Integer
Private asRightSide(1 To 10) As String
Private Obj As Object

Private Sub Command1_Click()
    Dim i As Integer
    aiLeftSide = aiRightSide
    asLeftSide = asRightSide
    For i = 1 To UBound(aiLeftSide)
        Debug.Print aiLeftSide(i)
    Next i
    For i = 1 To UBound(asLeftSide)
        Debug.Print asLeftSide(i)
    Next
End Sub

Private Sub Command2_Click()
    Dim i As Integer
    Dim aaint() As Variant
    Dim astring() As String
   
    astring = ReturnStringArray
    aaint = ReturnIntArray
    For i = 1 To UBound(aaint)
        Debug.Print aaint(i)
    Next i
    For i = 1 To UBound(astring)
        Debug.Print astring(i)
    Next i
End Sub

Private Sub Command3_Click()
    Dim astr() As String
    Dim aint() As Variant
    Dim i As Integer
    astr = Obj.ArrayFromClass
    For i = 1 To UBound(astr)
        Debug.Print "element_class " & astr(i)
    Next i
    aint = Obj.ArrayInt
    For i = 1 To UBound(aint)
        Debug.Print "int_class " & aint(i)
    Next i
End Sub

Private Sub Form_Load()
    Dim i As Integer
    Command1.Caption = "Assign Array"
    Command2.Caption = "Call Function that returns Array"
    Command3.Caption = "Call Object method that returns Array"
    For i = 1 To 10
        aiRightSide(i) = i
        asRightSide(i) = "This is element " & Str(i)
    Next i
    Set Obj = New Class1
End Sub

Private Function ReturnStringArray() As String()
    Dim astring(1 To 10) As String
    Dim i As Integer
    For i = 1 To UBound(astring)
        astring(i) = "Element " & Str(i)
    Next i
    ReturnStringArray = astring()
End Function

Private Function ReturnIntArray() As Variant
    Dim aaint(1 To 3) As Variant
    Dim i As Integer
    For i = 1 To UBound(aaint)
        aaint(i) = i
    Next i
    ReturnIntArray = aaint()
End Function

6，调试代码，做各种修改并观察变化

特别要注意的是，仔细关注不同地方的代码关于变量的声明：对于数值型的数组（如整数、双精度），应该声明的类型是 variant。否则调试通不过

不过，这仅仅是我在自己的计算机上测试的结果。我注意到，不少的示例代码，并不是这样声明的。估计还有版本的问题。

 

【代码示例2】

按照示例1的方法，移植到excel中使用。类模块中的代码转移到activex dll中

即：在excel与dll之间传递数组（字串型和数值型），该数组是固定长度

1，新建一个Activex DLL

名称：prjGt

2，将下列代码写入其中的类模块

名称：Gy

Option Explicit
Public Function ArrayStr() As String()
    Dim aStr(1 To 5) As String
    Dim i As Integer
    For i = 1 To 5
        aStr(i) = "Ar_str" & Str(i)
    Next i
    ArrayStr = aStr()
End Function
Public Function ArrayInt() As Variant
    Dim aInt(1 To 5) As Variant
    Dim i As Integer
    For i = 1 To 5
        aInt(i) = i
    Next i
    ArrayInt = aInt
End Function

3，建立以excel文件

4，在excel中建立引用。如果dll文件在本机注册，可手工建立引用；如果没有在本机注册，用代码建立引用关系。此代码可写在thisworkbook下

5，将下面代码写入VBA编辑器中

Sub zz()
Dim i As Integer
Dim aa As prjGt.Gy
Set aa = New Gy
Dim astr() As String
    astr = aa.ArrayStr
    Range("b1").Resize(UBound(astr), 1) = Application.Transpose(astr)
Dim aint() As Variant
    aint = aa.ArrayInt
    Range("d1").Resize(UBound(astr), 1) = Application.Transpose(aint)
End Sub

6，调试，并做各种修改观察变化