[發問] 請問 VBA 如何 add Object 到 ArrayList "ArrayList.Add(Object)"

solitary0720

Type Test
    Name As String
    Num As Integer
    Bool As Boolean
End Type

Public ArrList As New ArrayList
   
Sub AddArrayList()
    Dim Test1 as Test
    Test1 .Name ="sophia"
    Test1.Num=3
    Test1.Bool=True
   
    ArrList .Add(Test1)    '''這邊會秀出error
    ''' SHOW:
    '''Compile erroe:  only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions.
EndSub
請問有什麼方法可以添加使用者自定型態到Arraylist
還是只能Name , Num , Bool 分別存放在不同的Arraylist

stillfish00

回復 1# solitary0720
試試看用物件類別模組 取代 User-Defined Type

定義物件類別模組Test

1. Public Name As String
   
2. Public Num As Integer
   
3. Public Bool As Boolean

複製代碼

solitary0720

回復 2# stillfish00


    請問 物件"類別"模組 取代 User-Defined Type
    是指如下定義嗎

    Class Test
        Public s_Name as String
        Public s_Num as Integer
        Public s_Bool as Boolen

        Public Property Let Name (str As String)
              s_Name = str
        End Property
        Public Property Get Name () As String
            Name = s_Name
        End Property
    End Class
   
Public ArrList As New ArrayList
Function Body() as string
    Dim Test_1 as Test
    Set Test_1 = New Test
    Test_1.Name = "sophia"

    ArrList.Add(Test_1)
   '''''還是有問題 Show Run-time error "438"
   '''''Object doesn't support this property or method

End Function

謝謝~麻煩您了

stillfish00

回復 3# solitary0720
是指 VBA project 插入物件類別模組後，Class1 改名稱為 Test，填上

1. Public Name As String
   
2. Public Num As Integer
   
3. Public Bool As Boolean

複製代碼

因為我不知道ArrayList在設定引用項目要勾選哪個
故改用CreateObject去late-binding，
一般模組中:

1.    
   
2. Public ArrList As Object  'New ArrayList
   
3. Function Body() As String
   
4.     Dim Test_1 As Test
   
5. 
   
6.     Set ArrList = CreateObject("system.collections.arraylist")
   
7.     Set Test_1 = New Test
   
8. 
   
9.     Test_1.Name = "sophia"   
   
10.     ArrList.Add Test_1
    
11. 
    
12. End Function

複製代碼

話說回來，一般VBA中比較常見的不是 arraylist ，而是用 array 或 dictionary。

solitary0720

回復 4# stillfish00


    可以了!!!
    謝謝
    我發現  我的問題是()
    ArrList.Add (Test_1)
    會error

    ArrList.Add Test_1
    就可以了
    非常感謝

solitary0720

回復 4# stillfish00


   不好意思
   再請問
   空的array 定義不是Nothing嗎?

   Public ArrList(5) As address

Sub body()
    Dim Test1 As New address
    Dim Test2 As New address
   
    Test1.Name = "sophia"
    Test1.Num = 3
    Test1.Bool = True
        
    Test2.Name = "bill"
    Test2.Num = 5
    Test2.Bool = False
   
    Set ArrList(0) = Test1
    Set ArrList(1) = Test2

    If ArrList(3) = Nothing Then   '''這邊會秀error  "Invalid use of object"
        Dim Test_Empty As New address
        Set ArrList(3) = Test_Empty
    End If

End Sub

是過判斷 "" , Empty 都不行

stillfish00

回復 6# solitary0720
If ArrList(3) is Nothing Then