Bài 17. SortedList

Blue Softs epl Liên hệ QC

befaint

|||||||||||||
Tham gia
6/1/11
Bài viết
12,608
Được thích
15,795
Bài 17. SortedList

(Danh sách các bài viết về VBA xem ở đây Index - Các bài viết về VBA)

SortedList là một thư viện nằm trong “System.Collections” của .NET Framework.
SortedList lưu trữ các Items theo Keys tương ứng thành cặp, được sắp xếp theo các Keys, cho phép truy cập tới Item theo Key hoặc chỉ mục (index) ứng với Item đó.
Yêu cầu: Hệ thống phải cài đặt .NET Framework v1.1 trở lên.

Nội dung chính gồm:

#1
1. Khai báo SortedList
2. Các thuộc tính
2.1. Count
2.2. Capacity
2.3. Item
3. Các phương thức
3.1. Add
3.2. Clear
3.3. Clone
3.4. Contains / ContainsKey

#2
3.5. ContainsValue
3.6. GetByIndex
3.7. GetKey
3.8. GetKeyList
3.9. GetValueList
3.10. IndexOfKey
3.11. IndexOfValue
3.12. Remove
3.13. RemoveAt
3.14. SetByIndex
3.15. ToString
3.16. TrimToSize

#3
4. Ứng dụng
-----------------

1. Khai báo SortedList
1.1. Kiểu khai báo sớm
(Không có Tooltip khi gọi SortedList, 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:
PHP:
Dim oSList  As New SortedList

1.2. Kiểu khai báo muộn
(Không có Tooltip khi gọi SortedList, không cần thiết lập trong Tools/References).
Khai báo trong code:
PHP:
Dim oSList As Object
Set oSList = CreateObject("System.Collections.SortedList")

2. Các thuộc tính
2.1. Count
PHP:
oSList.Count
Trả về số lượng phần tử (Items) thực sự tồn tại trong SortedList.
Ví dụ:
PHP:
Sub CountProperty()
    'oSList.Count'
    Dim oSList As Object, i As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    oSList.Add "Key-", Array(2, 5)
    MsgBox oSList.Count    '6'
End Sub

2.2. Capacity
PHP:
oSList.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à SortedList có thể lưu trữ.
+ Các lỗi xảy ra đặt số lượng các phần tử cho SortedList:
- Khi giá trị của Capacity nhỏ hơn số lượng phần từ đã tồn tại trong SortedList (count).
- Khi giá trị của Capacity trả về SortedList 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ụ:
PHP:
Sub CapacityProperty()
    'oSList.Capacity'
    Dim oSList As Object, vCnt As Long, vCty As Long, i As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    vCnt = oSList.Count       '5'
    vCty = oSList.capacity    '16'
    oSList.capacity = 5
    MsgBox oSList.capacity    '5'
End Sub

2.3. Item
PHP:
oSList.Item(Key)
'Hoặc:'
oSList(Key)
Trả về giá trị của Item theo Key chỉ định trong SortedList, hoặc gán giá trị mới cho Item theo Key của Item đó.
- Nếu gán một giá trị cho một Item theo một Key chưa tồn tại trong SortedList thì SortedList tự động thêm Key đó, và Item đó có giá trị vừa đưa vào.
Ví dụ:
PHP:
Sub ItemProperty()
    'oSList.Item(Key) '
    'oSList(Key) '
    Dim oSList As Object, i As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, ""
    Next i
    oSList.Item("Key-1") = 100
    MsgBox oSList("Key-1") '100'
    oSList("KeyA") = 20
    MsgBox oSList.Count     '6'
End Sub

3. Các phương thức
3.1. Add
PHP:
oSList.Add Key, Item
Thêm một Item vào SortedList theo Key chỉ định.
Key: Phải duy nhất trong SortedList và khác null. Key nhận kiểu dữ liệu hoặc là số hoặc là chuỗi.
Item: Nhận kiểu dữ liệu bất kỳ (kiểu số hoặc chuỗi, null), giá trị của Items có thể trùng nhau,có thể là một trị đơn, hoặc một mảng (array).
Chỉ số đầu tiên của Key/ Item trong SortedList bằng 0.
Khi một Item được thêm vào, nó được chèn vào SortedList theo thứ tự sắp xếp chính xác theo Keys, và chỉ mục (Index) của nó được thiết lập tương ứng phù hợp theo Key.

Ví dụ:
PHP:
Sub AddMethod()
    'oSList.Add Key, Item '
        'Key: OR number OR string, <>null'
        'Item: number, string, array'
        'First index = 0    '
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "5", 20
    oSList.Add "KeyA", "Item2"
    oSList.Add "KeyB", ""
    oSList.Add "KeyC", Array(12, 40)
End Sub

3.2. Clear
PHP:
oSortedList.Clear
Xóa toàn bộ các Items trong SortedList.
Ví dụ:
PHP:
Sub ClearMethod()
    'oSList.Clear'
    Dim oSList As Object, i As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add i, ""
    Next i
    oSList.Clear
    MsgBox oSList.Count    '0'
End Sub

3.3. Clone
PHP:
oSortedList.Clone
Tạo một bản sao từ SortedList đã có.
Ví dụ:
PHP:
Sub CloneMethod()
    'oSList.Clone'
    Dim oSList As Object, i As Long, newSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add i, ""
    Next i
    Set newSList = oSList.Clone
    MsgBox newSList.Count   '5'
End Sub

3.4. Contains / ContainsKey
PHP:
oSList.Contains(Key)
oSList.ContainsKey(Key)
Kiểm tra sự tồn tại của một Key trong SortedList. Trả về True nếu Key đã tồn tại trong SortedList, ngược lại trả về False.
Lưu ý: Key chỉ định kiểm tra phải cùng kiểu dữ liệu (số hoặc chuỗi) với kiểu dữ liệu của Key đã có trong SortedList, nếu không xảy ra lỗi.
Ví dụ:
PHP:
Sub ContainsMethod()
    'oSList.Contains(Key)'
    Dim oSList As Object, i As Long, sKey As String
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    sKey = "Key-2"
    MsgBox oSList.Contains(sKey)   'True'
End Sub

PHP:
Sub ContainsKeyMethod()
    'oSList.ContainsKey(Key)'
    Dim oSList As Object, i As Long, sKey As String
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    sKey = "Key-5"
    MsgBox oSList.ContainsKey(sKey)   'True'
End Sub
 

befaint

|||||||||||||
Tham gia
6/1/11
Bài viết
12,608
Được thích
15,795
#2

3.5. ContainsValue
PHP:
oSList.ContainsKey(Key)
Kiểm tra sự tồn tại giá trị của một Item trong SortedList. Trả về True nếu giá trị đó đã tồn tại trong SortedList, ngược lại trả về False.
Ví dụ:
PHP:
Sub ContainsValueMethod()
    'oSList.ContainsKey(Key)'
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "KeyA", 2
    oSList.Add "KeyB", ""
    oSList.Add "KeyC", "Item3"
    MsgBox oSList.ContainsValue(2)    'True'
    MsgBox oSList.ContainsValue("")   'True'
    MsgBox oSList.ContainsValue("Item4")   'False'
End Sub

3.6. GetByIndex
PHP:
oSList.GetByIndex(Index)
Trả về giá trị của một Item trong SortedList theo chỉ mục (Index) chỉ định.
Ví dụ:
PHP:
Sub GetByIndexMethod()
    'oSList.GetByIndex(Index) //Index = 0 : Count-1 / Return: Item'
    Dim oSList As Object, i As Long, sKey
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, "Value-" & i
    Next i
    sKey = oSList.GetByIndex(0)
    MsgBox sKey     '"Value-1"'
End Sub

3.7. GetKey
PHP:
oSList.GetKey(Index)
Trả về một Key có trong SortedList theo chỉ mục (Index) chỉ định.
Index: Có giá trị từ 0 tới Count-1.
Ví dụ:
PHP:
Sub GetKeyMethod()
    'oSList.GetKey(Index) //Index = 0 : Count-1 '
    Dim oSList As Object, i As Long, sKey
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    sKey = oSList.GetKey(0)
    MsgBox sKey     '"Key-1"'
End Sub

3.8. GetKeyList
PHP:
oSList.GetKeyList
Trả về một đối tượng IList (System.Collections.IList) chứa toàn bộ Keys có trong SortedList.
Ví dụ:
PHP:
Sub GetKeyListMethod()
    'oSList.GetKeyList '
    'Return an IList object (System.Collections.IList) containing the keys in the SortedList object.'
    Dim oSList As Object, i As Long, KeyList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    Set KeyList = oSList.GetKeyList
    MsgBox KeyList.Item(0)     '"Key-1"'
End Sub

3.9. GetValueList
PHP:
oSList.GetValueList
Trả về một đối tượng IList (System.Collections.IList) chứa toàn bộ Items có trong SortedList.
Ví dụ:
PHP:
Sub GetValueListMethod()
    'oSList.GetValueList '
    'Return an IList object (System.Collections.IList) containing the values in the SortedList object.'
    Dim oSList As Object, i As Long, ValueList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    For i = 1 To 5
        oSList.Add "Key-" & i, i
    Next i
    Set ValueList = oSList.GetValueList
    MsgBox ValueList.Item(4)     '5'
End Sub

3.10. IndexOfKey
PHP:
oSList.IndexOfKey(Key)
Trả về vị trí của Key chỉ định trong SortedList (FirstIndex = 0).
Nếu Key chỉ định không có trong SortedList thì kết quả trả về -1.

Các phần tử trong SortedList được sắp xếp chính xác theo Keys. Khi một phần tử được thêm, nó được chèn vào SortedList theo thứ tự sắp xếp chính xác, và lập chỉ mục (Index) điều chỉnh cho phù hợp. Khi một phần tử được loại bỏ, việc lập chỉ mục cũng điều chỉnh cho phù hợp. Do đó, chỉ mục của cặp Key/ Item cụ thể có thể thay đổi khi các phần tử được thêm vào hoặc xoá khỏi SortedList.
Ví dụ:
PHP:
Sub IndexOfKeyMethod()
    'oSList.IndexOfKey(Key)
    Dim oSList As Object, idex As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "1", ""
    oSList.Add "Z", ""
    oSList.Add "A", ""
    idex = oSList.IndexOfKey("1")   'idex=0'
    MsgBox idex
    idex = oSList.IndexOfKey("Z")   'idex=2'
    MsgBox idex
    idex = oSList.IndexOfKey("A")   'idex=1'
    MsgBox idex
End Sub

3.11. IndexOfValue
PHP:
oSList.IndexOfValue(Item)
Trả về vị trí của Item chỉ định trong SortedList (FirstIndex = 0).
Nếu Item chỉ định không có trong SortedList thì kết quả trả về -1.
Ví dụ:
PHP:
Sub IndexOfValueMethod()
    'oSList.IndexOfValue(Item)'
    Dim oSList As Object, idex As Long
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "1", 20
    oSList.Add "Z", 20
    oSList.Add "A", "Item3"
    idex = oSList.IndexOfValue(20)          'idex=0'
    MsgBox idex
    idex = oSList.IndexOfValue("Item3")     'idex=1'
    MsgBox idex
    idex = oSList.IndexOfValue("Item4")
    MsgBox idex                             'idex=-1'
End Sub

3.12. Remove
PHP:
oSList.Remove Key
Xóa một phần tử trong SortedList theo Key chỉ định. Nếu Key chưa tồn tại trong SortedList thì không xảy ra lỗi.

Khi một phần tử được loại bỏ, việc lập chỉ mục (Index) của các phần tử được điều chỉnh cho phù hợp. Do đó, chỉ mục của cặp Key/ Item cụ thể có thể thay đổi khi các phần tử được thêm vào hoặc xoá khỏi SortedList.
Ví dụ:
PHP:
Sub RemoveMethod()
    'oSList.Remove Key'
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "Key-1", ""
    oSList.Add "Key-2", ""
    oSList.Remove "Key-1"
    MsgBox oSList.GetKey(0)     ' "Key-2" '
End Sub

3.13. RemoveAt
PHP:
oSList.RemoveAt Index
Xóa một Item trong SortedList theo chỉ số Index chỉ định.
Index: Có giá trị từ 0 tới Count-1.
Ví dụ:
PHP:
Sub RemoveAtMethod()
    'oSList.RemoveAt Index //Index = 0 : Count-1 '
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "1", ""
    oSList.Add "Z", ""
    oSList.Add "A", ""
    oSList.RemoveAt 1
    MsgBox oSList.GetKey(1)     ' "Z" '
End Sub

3.14. SetByIndex
PHP:
oSList.SetByIndex Index, Item
Thay đổi giá trị của Item trong SortedList theo chỉ mục (Index) chỉ định.
Ví dụ:
PHP:
Sub SetByIndexMethod()
    'oSList.SetByIndex Index, Item //Index = 0 : Count-1 '
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add 0, 100
    oSList.Add 1, 500
    oSList.Add 2, 10
    oSList.SetByIndex 0, "Item-1"
    MsgBox oSList.GetByIndex(0)
End Sub

3.15. ToString
PHP:
oSList.ToString
Trả về tên đối tượng hiện hành, tức là “System.Collections.SortedList”.
Ví dụ:
PHP:
Sub ToStringMethod()
    'oSList.ToString '
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    MsgBox oSList.ToString        '"System.Collections.SortedList"'
End Sub

3.16. TrimToSize
PHP:
oSList.TrimToSize
Thiết lập số phần tử tồn tại thực tế có trong SortedList.
Khi sử dụng phương thức TrimToSize thì Capacity = Count.

Để thiết lập lại SortedList 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 SortedList trống sẽ đặt Capacity của SortedList về giá trị mặc định.
Ví dụ:
PHP:
Sub TrimToSizeMethod()
    'oSList.TrimToSize '
    Dim oSList As Object
    Set oSList = CreateObject("System.Collections.SortedList")
    oSList.Add "Key-1", 1
    oSList.Add "Key-2", 2
    MsgBox "Count = " & oSList.Count & vbNewLine & _
            "Capacity = " & oSList.capacity   '2-16'
    oSList.TrimToSize
    MsgBox "Count = " & oSList.Count & vbNewLine & _
            "Capacity = " & oSList.capacity   '2-2'
End Sub
 
Upvote 0

befaint

|||||||||||||
Tham gia
6/1/11
Bài viết
12,608
Được thích
15,795
#3

4. Ứng dụng
Lọc loại trùng, sort mảng, …

4.1. Một số hàm
Hàm lọc loại trùng 1 cột của Range
PHP:
'// Loc loai trung mot cot'
Function UniqueColumnSortedList(ByVal Rng As Range) As Variant
    If Rng.Count = 1 Then UniqueColumnSortedList = Rng.Value: Exit Function
    Dim oSList As Object, i As Long, j As Long, Arr(), Result(), sKey As Variant
    Set oSList = CreateObject("System.Collections.SortedList")
    Arr = Rng.Value
    For i = LBound(Arr, 1) To UBound(Arr, 1)
        sKey = Arr(i, 1)
        If sKey <> "" Then
            If oSList.ContainsKey(sKey) = False Then
                oSList.Add sKey, ""
                j = j + 1
                ReDim Preserve Result(1 To j)
                Result(j) = sKey
            End If
        End If
    Next i
    UniqueColumnSortedList = Result
End Function

Hàm lọc loại trùng và sort mảng 1 chiều
PHP:
'// Loc loai trung va sort mang 1 chieu'
Function Sort1DSortedList(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 oSList As Object, iTemArr
    Set oSList = CreateObject("System.Collections.SortedList")
    For Each iTemArr In Source1D
        If IsNumber = False Then
            iTemArr = CStr(iTemArr)
        Else
            If IsNumeric(iTemArr) = False Then GoTo NextCode
        End If
        If oSList.ContainsKey(iTemArr) = False Then
                oSList.Add iTemArr, ""
        End If
NextCode:
    Next iTemArr
    Sort1DSortedList = GetKeysSortedList(oSList, Order)
End Function

Hàm lấy toàn bộ Keys trong SortedList vào mảng 1 chiều
PHP:
'// Lay Keys trong SortedList vao array 1D'
Function GetKeysSortedList(oSList As Object, Optional ByVal Order As Boolean = True)
'Order: True - Sort A-Z, False - Sort Z-A'
    Dim Result(), i As Long, index As Long, numKeys As Long
    numKeys = oSList.Count
    ReDim Result(1 To numKeys)
    For i = 1 To numKeys
        If Order = True Then index = i - 1 Else index = numKeys - i
        Result(i) = oSList.GetKey(index)
    Next i
    GetKeysSortedList = Result
End Function

Hàm lấy toàn bộ Items trong SortedList vào mảng 1 chiều
PHP:
'// Lay Items trong SortedList vao array 1D'
Function GetItemsSortedList(oSList As Object, Optional ByVal Order As Boolean = True)
'Order: True - Sort A-Z, False - Sort Z-A'
    Dim Result(), i As Long, index As Long, numItems As Long
    numItems = oSList.Count
    ReDim Result(1 To numItems)
    For i = 1 To numItems
        If Order = True Then index = i - 1 Else index = numItems - i
        Result(i) = oSList.Item(oSList.GetKey(index))
    Next i
    GetItemsSortedList = Result
End Function

4.2. Ví dụ:
Bài toán lọc loại trùng cột mã, tính tổng và sắp xếp (Z-A) cột số lượng.
(Xem trong file đính kèm).

 

File đính kèm

  • SortedList.xlsb
    1.2 MB · Đọc: 61
Lần chỉnh sửa cuối:
Upvote 0

Ca Điền Tâm

Thành viên thường trực
Tham gia
10/6/17
Bài viết
342
Được thích
381
Giới tính
Nam
Nghề nghiệp
làm thuê
Cái này của Anh rất hay, nhưng mà để tiêu hóa cho hết thì em cũng cần có thời gian.Cám ơn Anh rất nhiều
 
Upvote 0
Web KT
Top Bottom