VBA Excel ArrayList Excel VBA

Đánh giá lequocthai.com:

Voted: 0 Votes: 0

Your page rank:

ArrayList là một thư viện nằm trong “System.Collections” của .NET Framework. Cho phép lưu trữ dữ liệu (items) có kích cỡ lớn, kích thước của mảng lưu trữ tự động tăng theo yêu cầu, cho phép truy xuất tới các items, sắp xếp các items đã nạp vào ArrayLis.
Yêu cầu: Hệ thống phải cài đặt .NET Framework v1.1 trở lên.

1. Khai báo ArrayList

1.1. Kiểu khai báo sớm

(Không có Tooltip khi gọi ArrayList, phải thiết lập trong Tools/References)
– Trong cửa sổ VBA, Tools menu, References.
– Tìm và check vào mục “mscorlib.dll” trong cửa sổ References – VBAProject.
Khai báo trong code:

Dim oArrList As New ArrayList

1.2. Kiểu khai báo muộn

(Không có Tooltip khi gọi ArrayList, không cần thiết lập trong Tools/References).
Khai báo trong code:

Dim oArrList As Object
Set oArrList = CreateObject("System.Collections.ArrayList")

2. Các thuộc tính

2.1. Count

oArrList.Count

Trả về số lượng phần tử (Items) thực sự tồn tại trong ArrayList.
Ví dụ:

Sub CountProperty()
    'oArrList.Count'
    Dim oArrList As Object, i As Long, arr
    Set oArrList = CreateObject("System.Collections.ArrayList")
    arr = Array(20, 50)
    For i = 1 To 5
        oArrList.Add i
    Next i
    oArrList.Add arr
    MsgBox oArrList.Count    '6'
End Sub

2.2. Capacity

oArrList.Capacity

Trả về số lượng các phần tử hoặc đặt (thiết lập) số lượng các phần tử mà ArrayList có thể lưu trữ.
+ Các lỗi xảy ra đặt số lượng các phần tử cho ArrayList:
– Khi giá trị của Capacity nhỏ hơn số lượng phần từ đã tồn tại trong ArrayList (count).
– Khi giá trị của Capacity trả về ArrayList có kích thước vượt dung lượng bộ nhớ có thể dùng của hệ thống (OutOfMemoryException).
+ Capacity luôn luôn lớn hơn hoặc bằng Count. Nếu Count vượt quá Capacity trong khi thêm các phần tử, Capacity sẽ tự động tăng lên bằng cách tái phân bổ lại mảng nội bộ trước khi sao chép các phần tử cũ và thêm các phần tử mới.
+ Có thể giảm Capacity bằng cách dùng phương thức TrimToSize hoặc bằng cách thiết lập giá trị Capacity cụ thể (nhưng phải đảm bảo >= Count, nếu không sẽ xảy ra lỗi). Khi giá trị của Capacity được xác định cụ thể, mảng nội bộ cũng được phân bổ lại để đáp ứng dung lượng đã xác định.
Ví dụ:

Sub CapacityProperty()
    'oArrList.Capacity'
    Dim oArrList As Object, vCnt As Long, vCty As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For i = 1 To 5
        oArrList.Add i
    Next i
    vCnt = oArrList.Count       '5'
    vCty = oArrList.Capacity    '8'
    oArrList.Capacity = 5
    MsgBox oArrList.Capacity    '5'
End Sub

2.3. Item

oArrList.Item(Index)

Trả về giá trị của Item theo chỉ số (index) chỉ định trong ArrayList, hoặc gán giá trị mới cho Item theo chỉ số của Item đó.
Lưu ý: Chỉ số (index) của Item có giá trị từ 0 tới Count-1.
Ví dụ:

Sub ItemProperty()
    'oArrList.Item(Index)     -FirstIndex = 0'
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For i = 1 To 5
        oArrList.Add "Value-" & i
    Next i
    oArrList.Item(4) = 100
    MsgBox oArrList.Item(0) 'Value-1'
    MsgBox oArrList.Item(4) '100'
End Sub

3. Các phương thức

3.1. Add

oArrayList.Add Item

Thêm một Item vào vị trí cuối cùng (end) của ArrayList.
Item có thể là một trị đơn, hoặc một mảng (array).
Item nhận kiểu dữ liệu bất kỳ (kiểu số hoặc chuỗi), giá trị của Items có thể trùng nhau.
Chỉ số đầu tiên của Item trong ArrayList bằng 0, cho dù Option Base 1.
Ví dụ:

Sub AddMethod()
    'oArrList.Add Item   -Adds a item to the end of the ArrayList'
        'Item: number, string, array'
        'First index = 0'
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    Dim Arr1, Arr2(1 To 2, 1 To 1), itemArr, itemList
    Arr1 = Array(10, 12)
    Arr2(1, 1) = 50
    Arr2(2, 1) = "Example"
    'Adds items to oArrList:'
    oArrList.Add 20
    oArrList.Add "TextA"
    oArrList.Add Arr1
    oArrList.Add Arr2

    For Each itemList In oArrList
        If IsArray(itemList) = True Then
            For Each itemArr In itemList
                MsgBox itemArr
            Next itemArr
        Else
            MsgBox itemList
        End If
    Next itemList
    'Result: 20, "TextA", 10, 12, 50 , "Example" '
End Sub

3.2. AddRange

oArrList.AddRange ICollection

Thêm một đối tượng ICollection vào vị trí cuối cùng của ArrayList.
Icollection: Có thể là Stack, Queue, Hashtable, SortList, ArrayList
Ví dụ:

Sub AddRangeMethod()
    'Adds the elements of an ICollection to the end of the ArrayList.'
    'oArrList.AddRange ICollection'
    Dim oArrList As Object, ArList1 As Object, itemList
    Set oArrList = CreateObject("System.Collections.ArrayList")
    Set ArList1 = CreateObject("System.Collections.ArrayList")
    ArList1.Add 10
    ArList1.Add 20
    oArrList.Addrange ArList1
    For Each itemList In oArrList
        MsgBox itemList
    Next
    'Result: 10, 20'
End Sub

3.3. Clear

oArrayList.Clear

Xóa toàn bộ các Items trong ArrayList.
Ví dụ:

Sub ClearMethod()
    'oArrList.Clear'
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For i = 1 To 5
        oArrList.Add i
    Next i
    oArrList.Clear
    MsgBox oArrList.Count    '0'
End Sub

3.4. Clone

oArrayList.Clone

Tạo một bản sao từ ArrayList đã có.
Ví dụ:

Sub CloneMethod()
    'oArrList.Clone'
    Dim oArrList As Object, i As Long, newArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For i = 1 To 5
        oArrList.Add i
    Next i
    Set newArrList = oArrList.Clone
    MsgBox newArrList.Count '5'
End Sub

3.5. Contains

oArrList.Contains(Item)

Kiểm tra sự tồn tại của một Item trong ArrayList. Trả về True nếu Item đã tồn tại trong ArrayList, ngược lại trả về False.
Ví dụ:

Sub ContainsMethod()
    'oArrList.Contains(Item)'
    Dim oArrList As Object, i As Long, valItem
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For i = 1 To 5
        oArrList.Add "Value-" & i
    Next i
    valItem = oArrList(0)
    MsgBox oArrList.Contains(valItem)   'True'
End Sub

3.6. IndexOf

oArrList.IndexOf(Item, index)

Trả về vị trí đầu tiên tìm thấy Item chỉ định, dò tìm từ vị trí Index chỉ định so với vị trí đầu tiên trong ArrayList (FirstIndex = 0).
Ví dụ:

Sub IndexOf()
    'oArrList.IndexOf(Item, index)'
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-1"
    i = oArrList.IndexOf("Value-1", 0) 'i=0'
    i = oArrList.IndexOf("Value-1", 1) 'i=3'
    i = oArrList.IndexOf("Value-1", 4) 'i=5'
End Sub

3.7. Insert

oArrList.Insert Index, Item

Chèn thêm một Item vào ArrayList theo vị trí (Index) chỉ định.
Index: Nhận giá trị từ 0 tới oArrList.Count. Gặp lỗi khi Index < 0 hoặc Index > Count.
Ví dụ:

Sub InsertMethod()
    'oArrList.Insert Index, Item'
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Insert 0, 100
    oArrList.Insert 1, 200
    For i = 1 To 5
        oArrList.Add i
    Next i
    oArrList.Insert 4, 30
    oArrList.Insert oArrList.Count, 50
    MsgBox oArrList.Item(oArrList.Count - 1)    '50'
End Sub

3.8. InsertRange

oArrList.InsertRange Index, ICollection

Chèn thêm một đối tượng ICollection vào ArrayList theo vị trí (Index) chỉ định.
Index: Nhận giá trị từ 0 tới Count.
Ví dụ:

Sub InsertRangeMethod()
    'Inserts the elements of an ICollection into the ArrayList at the specified index.'
    'oArrList.InsertRange Index, ICollection'
    Dim oArrList As Object, ArList1 As Object, itemList
    Set oArrList = CreateObject("System.Collections.ArrayList")
    Set ArList1 = CreateObject("System.Collections.ArrayList")
    ArList1.Add 10
    ArList1.Add 20
    oArrList.Add "Item1"
    oArrList.InsertRange 1, ArList1
    For Each itemList In oArrList
        MsgBox itemList
    Next
    'Result: "Item1", 10, 20'
End Sub

3.9. LastIndexOf

oArrList.LastIndexOf(Item)

Trả về vị trí (Index) cuối cùng của Item tìm thấy trong ArrayList.
Ví dụ:

Sub LastIndexOfMethod()
    'oArrList.LastIndexOf(Item)'
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-1"
    i = oArrList.LastIndexOf("Value-1") 'i=5'
End Sub

3.10. Remove

oArrList.Remove Item

Xóa một Item chỉ định trong ArrayList. Nếu Item chưa tồn tại trong ArrayList thì không xảy ra lỗi.
Nếu Item có nhiều giá trị như nhau thì sẽ xóa Item xuất hiện đầu tiên.
Ví dụ:

Sub RemoveMethod()
    'oArrList.Remove Item'
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Remove "Value-1"
    MsgBox oArrList.Item(0)     ' "Value-2" '
End Sub

3.11. RemoveAt

oArrList.RemoveAt Index

Xóa một Item trong ArrayList theo chỉ số Index chỉ định.
Index: Có giá trị từ 0 tới Count-1.
Ví dụ:

Sub RemoveAtMethod()
    'oArrList.RemoveAt Index'
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-4"
    oArrList.RemoveAt 2
    MsgBox oArrList.Item(2)     ' "Value-4" '
End Sub

3.12. RemoveRange

oArrList.RemoveRange IndexFrom, NumberOfItems

Xóa các Items có trong ArrayList từ vị trí chỉ định, số lượng Items cần xóa được chỉ định.
IndexFrom: Vị trí Item đầu tiên cần xóa. Index có giá trị từ 0 tới Count-1.
NumberOfItems: Số Item cần xóa. NumberOfItems có giá trị từ 0 tới (Count – Index).
Ví dụ:

Sub RemoveRangeMethod()
    'oArrList.RemoveRange IndexFrom, NumberOfItems '
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-4"
    oArrList.RemoveRange 0, 2
    MsgBox oArrList.Item(0)     ' "Value-3" '
End Sub

3.13. Reverse

oArrList.Reverse

Đảo ngược vị trí toàn bộ các Items có trong ArrayList.
Ví dụ:

Sub ReverseMethod()
    'oArrList.Reverse '
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    oArrList.Add "Value-3"
    oArrList.Add "Value-4"
    oArrList.Reverse
    For i = 0 To oArrList.Count - 1
        MsgBox oArrList.Item(i) ' "Value-4","Value-3","Value-2","Value-1"   '
    Next i
End Sub

3.14. Sort

oArrList.Sort

Sắp xếp các Items trong ArrayList theo thứ tự A-Z.
Ví dụ:

Sub SortMethod()
    'oArrList.Sort '
    Dim oArrList As Object, i As Long
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-3"
    oArrList.Add "Value-2"
    oArrList.Add "Value-4"
    oArrList.Add "Value-0"
    oArrList.Sort
    For i = 0 To oArrList.Count - 1
        MsgBox oArrList.Item(i) '"Value-0", "Value-1","Value-2","Value-3","Value-4"'
    Next i
End Sub

3.15. ToArray

oArrList.ToArray

Sao chép các Item trong ArrayList vào một mảng (Array). Mảng trả về là mảng một chiều, chỉ số cận dưới của mảng luôn băng 0, cho dù thiết lập Option Base 1.
Ví dụ:

Sub ToArrayMethod()
    'oArrList.ToArray '
    Dim oArrList As Object, Arr()
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add Array(15, 40)
    oArrList.Add 200
    Arr = oArrList.ToArray
End Sub

3.16. ToString

oArrList.ToString

Trả về tên đối tượng hiện hành, tức là “System.Collections.ArrayList”.
Ví dụ:

Sub ToStringMethod()
    'oArrList.ToString '
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    MsgBox oArrList.ToString        '"System.Collections.ArrayList"'
End Sub

3.17. TrimToSize

oArrList.TrimToSize

Sử dụng để giảm vùng nhớ của Collection nếu không có Item mới sẽ được thêm vào.
Khi sử dụng phương thức TrimToSize thì Capacity = Count.

Để thiết lập lại ArrayList về trạng thái ban đầu, ta sử dụng phương thức Clear trước khi gọi TrimToSize. Việc cắt một ArrayList trống sẽ đặt Capacity của ArrayList về giá trị mặc định.
Ví dụ:

Sub TrimToSizeMethod()
    'oArrList.TrimToSize '
    Dim oArrList As Object
    Set oArrList = CreateObject("System.Collections.ArrayList")
    oArrList.Add "Value-1"
    oArrList.Add "Value-2"
    MsgBox "Count = " & oArrList.Count & vbNewLine & _
            "Capacity = " & oArrList.Capacity   '2-4'
    oArrList.TrimToSize
    MsgBox "Count = " & oArrList.Count & vbNewLine & _
            "Capacity = " & oArrList.Capacity   '2-2'
End Sub

4. Ứng dụng

Lọc loại trùng, sort mảng, tạo số ngẫu nhiên không trùng…

4.1. Một số hàm

Hàm lọc loại trùng 1 cột của Range

'// Loc loai trung mot cot'
Function UniqueColumnArrayList(ByVal Rng As Range) As Variant
    If Rng.Count = 1 Then UniqueColumnArrayList = Rng.Value: Exit Function
    Dim oArrList As Object, i As Long, j As Long, Arr(), Result(), sKey As Variant
    Set oArrList = CreateObject("System.Collections.ArrayList")
    Arr = Rng.Value
    For i = LBound(Arr, 1) To UBound(Arr, 1)
        sKey = Arr(i, 1)
        If sKey <> "" Then
            If oArrList.Contains(sKey) = False Then
                oArrList.Add sKey
                j = j + 1
                ReDim Preserve Result(1 To j)
                Result(j) = sKey
            End If
        End If
    Next i
    UniqueColumnArrayList = Result
End Function

Hàm Sort mảng 1 chiều

'// Sort mang 1 chieu'
Function Sort1DArrayList(ByVal Source1D, Optional ByVal IsNumber As Boolean = True, _
                        Optional ByVal Order As Boolean = True) As Variant
'IsNumber: True - Du lieu kieu so, False - Du lieu kieu chuoi'
'Order: True - Sort A-Z, False - Sort Z-A'
    If IsArray(Source1D) = False Then Exit Function
    Dim oArrList As Object, itemArr
    Set oArrList = CreateObject("System.Collections.ArrayList")
    For Each itemArr In Source1D
        If IsNumber = False Then itemArr = CStr(itemArr)
        oArrList.Add itemArr
    Next itemArr
    oArrList.Sort
    If Order = False Then oArrList.Reverse
    Sort1DArrayList = oArrList.ToArray
End Function

Tải file ví dụ: ArrayList