- Loạt các bài viết có liên quan chuyên đề VBA:
- VBA Excel Hộp thoại thông báo
- VBA Excel Biến trong VBA Excel
- VBA Excel sử dụng Scripting Dictionary
- VBA Excel Biến trong VBA Excel
- VBA Excel Workbook, worksheet
- VBA Excel Range, Cells
- VBA Excel Hàm trong Excel VBA
- VBA Excel Events, Application Object
- VBA Excel Function and Sub Excel VBA
- VBA Excel Array Mảng trong Excel VBA
- VBA Excel FileSystemObject trong Excel VBA
- VBA Excel Collection trong Excel VBA
- VBA Excel Hashtable trong Excel VBA
- VBA Excel Stack trong Excel VBA
- VBA Excel Queue trong Excel VBA
- VBA Excel SortedList Excel VBA
- VBA Excel ArrayList Excel VBA
- VBA Excel Ví dụ về Scripting Dictionary
- 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
