Home » VBA Excel » VBA Excel SortedList Excel VBA

VBA Excel SortedList Excel VBA

viết bởi Lê Quốc Thái
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)

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.

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:

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: 

Dim oSList As Object
Set oSList = CreateObject("System.Collections.SortedList")

2. Các thuộc tính

 

2.1. Count

oSList.Count

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

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
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ụ:

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
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ụ:

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

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ụ:

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

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

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

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

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
 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ụ:

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

 

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
3.5. ContainsValue
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ụ:

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
oSList.GetByIndex(Index)

Trả về giá trị của một Item trong SortedList theo chỉ mục (Index) chỉ định.
Ví dụ:

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
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ụ:

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

Trả về một đối tượng IList (System.Collections.IList) chứa toàn bộ Keys có trong SortedList.
Ví dụ:

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

Trả về một đối tượng IList (System.Collections.IList) chứa toàn bộ Items có trong SortedList.
Ví dụ:

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
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ụ:

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
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ụ:

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
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ụ:

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
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ụ:

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
oSList.SetByIndex Index, Item

Thay đổi giá trị của Item trong SortedList theo chỉ mục (Index) chỉ định.
Ví dụ:

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

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

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
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ụ:

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

'// 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

'// 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

'// 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

'// 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

 

Tải file ví dụ: SortedList

Bài viết liên quan

Viết ý kiến của bạn