Tạo Và Sử Dụng Scripting.dictionary Trong Vba Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Cách Tạo Mã Qr Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu

Trong môi trường lập trình VBA (Visual Basic for Applications) cho Excel, việc quản lý dữ liệu một cách hiệu quả là yếu tố then chốt để xây dựng các ứng dụng mạnh mẽ và nhanh chóng. Một trong những công cụ mạnh mẽ nhưng lại ít được khai thác đầy đủ là Scripting.Dictionary – một đối tượng cung cấp khả năng lưu trữ cặp khóa-giá trị (key‑value) tương tự như Dictionary trong các ngôn ngữ lập trình hiện đại. Bài viết này sẽ đi sâu vào cách CreateObject(“Scripting.Dictionary”), cách khởi tạo, các phương thức, thuộc tính quan trọng, so sánh với các cấu trúc dữ liệu khác, và thực tế áp dụng trong các bài toán thực tế trên Excel.

Lưu ý: Để sử dụng Scripting.Dictionary, máy tính của bạn cần có thư viện Microsoft Scripting Runtime (scrrun.dll). Bạn có thể tham chiếu trực tiếp hoặc tạo đối tượng bằng CreateObject. Hai cách này sẽ được trình bày chi tiết.

Có thể bạn quan tâm: Cách Tạo Sheet Mới Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới Thiệu Tổng Quan Về Scripting.Dictionary

1.1. Định Nghĩa

Scripting.Dictionary là một đối tượng COM (Component Object Model) thuộc thư viện Microsoft Scripting Runtime. Nó hoạt động như một bảng băm (hash table) cho phép:

• Lưu trữ các cặp Key (khóa) và Item (giá trị).
• Truy cập nhanh chóng tới giá trị dựa trên khóa (thời gian truy cập gần như O(1)).
• Thêm, xóa, kiểm tra tồn tại của khóa một cách linh hoạt.
• Lưu trữ bất kỳ kiểu dữ liệu nào: số, chuỗi, mảng, đối tượng, thậm chí là các Collection hoặc Dictionary lồng nhau.

1.2. Lợi Ích Khi Dùng Dictionary Thay Vì Mảng/Collection

Như vậy, khi bạn cần tra cứu nhanh dựa trên một giá trị duy nhất (ví dụ: mã sản phẩm, ID nhân viên), Dictionary là lựa chọn tối ưu.

Có thể bạn quan tâm: Cách Tạo Và Quản Lý Name Range Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Cách Khởi Tạo Scripting.Dictionary

2.1. Sử Dụng CreateObject

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

• Ưu điểm: Không cần thêm tham chiếu trong Tools → References, nên mã vẫn chạy trên mọi máy có Windows và Office.
• Nhược điểm: Không có IntelliSense (gợi ý code) trong môi trường VBA, vì dict được khai báo là Object.

2.2. Tham Chiếu Trực Tiếp (Early Binding)

1. Vào Tools → References.
2. Tìm và tick Microsoft Scripting Runtime (thường là scrrun.dll).
3. Khai báo:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

• Ưu điểm: IntelliSense, kiểm tra lỗi khi biên dịch, tốc độ thực thi hơi nhanh hơn.
• Nhược điểm: Cần chắc chắn rằng thư viện có trên máy người dùng; nếu không, sẽ gặp lỗi “Reference not found”.

2.3. So Sánh Hai Cách

Trong thực tế, phần lớn các dự án VBA sử dụng CreateObject để tránh phụ thuộc vào thư viện, trừ khi dự án nội bộ và kiểm soát môi trường.

3. Các Thuộc Tính và Phương Thức Cơ Bản

Sau khi khởi tạo, Dictionary cung cấp một tập hợp phương thức và thuộc tính. Dưới đây là danh sách chi tiết cùng ví dụ thực tế.

3.1. Thuộc Tính

3.2. Phương Thức Quan Trọng

Lưu ý: Khi dùng Add, nếu key đã tồn tại sẽ gây lỗi runtime “Key already exists”. Thường dùng If Not dict.Exists(key) Then dict.Add key, value.

4. Cấu Hình CompareMode: So Sánh Nhạy Cảm Hay Không?

Dictionary cho phép bạn xác định cách so sánh khóa thông qua thuộc tính CompareMode. Hai hằng số chính:

• vbBinaryCompare (0): So sánh nhạy cảm chữ hoa/thường (binary). "A" ≠ "a".
• vbTextCompare (1): So sánh không phân biệt hoa thường. "A" = "a".

Ví dụ thực tế:

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare ' Không phân biệt hoa thường d.Add "apple", 1
Debug.Print d.Exists("APPLE") ' True

Nếu bạn quên đặt CompareMode và làm việc với dữ liệu không đồng nhất, có thể gặp lỗi “Key already exists” hoặc “Key not found”.

5. Thực Hành: 10 Bài Tập Thực Tế Với Dictionary trong Excel

5.1. Đếm Số Lần Xuất Hiện Của Mỗi Giá Trị Trong Cột

Sub CountOccurrences() Dim ws As Worksheet, rng As Range, cell As Range Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Set ws = ThisWorkbook.Worksheets("Sheet1") Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) For Each cell In rng If Not IsError(cell.Value) Then If dict.Exists(cell.Value) Then dict(cell.Value) = dict(cell.Value) + 1 Else dict.Add cell.Value, 1 End If End If Next cell ' Ghi kết quả vào cột B Dim i As Long: i = 2 For Each key In dict.Keys ws.Cells(i, "B").Value = key ws.Cells(i, "C").Value = dict(key) i = i + 1 Next key
End Sub

5.2. Loại Bỏ Các Giá Trị Trùng Lặp (Unique List)

Sub UniqueList() Dim src As Range, dest As Range, cell As Range Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Set src = ThisWorkbook.Worksheets("Data").Range("A2:A100") Set dest = ThisWorkbook.Worksheets("Result").Range("A2") For Each cell In src If Len(cell.Value) > 0 Then dict(cell.Value) = Empty Next cell dest.Resize(dict.Count, 1).Value = Application.Transpose(dict.Keys)
End Sub

5.3. Tìm Giá Trị Trùng Lặp Giữa Hai Cột

Sub FindDuplicatesBetweenColumns() Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim rng1 As Range, rng2 As Range, cell As Range Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") Set rng1 = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) Set rng2 = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row) For Each cell In rng1 If Len(cell.Value) > 0 Then dict(cell.Value) = 1 Next cell Dim outputRow As Long: outputRow = 2 For Each cell In rng2 If dict.Exists(cell.Value) Then ws.Cells(outputRow, "D").Value = cell.Value ' Ghi vào cột D outputRow = outputRow + 1 End If Next cell
End Sub

5.4. Tạo Lookup Table Từ Một Bảng Dữ Liệu

Function CreateLookupDict(rng As Range) As Object Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim i As Long, key As Variant, val As Variant For i = 1 To rng.Rows.Count key = rng.Cells(i, 1).Value ' Cột 1 là khóa val = rng.Cells(i, 2).Value ' Cột 2 là giá trị If Not dict.Exists(key) Then dict.Add key, val Next i Set CreateLookupDict = dict
End Function Sub UseLookup() Dim dict As Object Set dict = CreateLookupDict(ThisWorkbook.Sheets("Lookup").Range("A2:B100")) ' Ví dụ: tra cứu giá trị dựa trên ô C2 Dim lookupKey As Variant: lookupKey = ThisWorkbook.Sheets("Sheet1").Range("C2").Value If dict.Exists(lookupKey) Then MsgBox "Giá trị là: " & dict(lookupKey) Else MsgBox "Không tìm thấy khóa." End If
End Sub

5.5. Tổng Hợp Dữ Liệu Theo Nhóm (Group By)

Giả sử có bảng: Mã khách hàng (cột A) và Doanh thu (cột B). Ta muốn tính tổng doanh thu cho mỗi khách hàng.

Sub GroupByCustomer() Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sales") Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim lastRow As Long, i As Long, cust As String, rev As Double lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow cust = ws.Cells(i, "A").Value rev = ws.Cells(i, "B").Value If dict.Exists(cust) Then dict(cust) = dict(cust) + rev Else dict.Add cust, rev End If Next i ' Ghi kết quả vào Sheet2 Dim outWs As Worksheet: Set outWs = ThisWorkbook.Sheets("Summary") outWs.Range("A2:B" & outWs.Rows.Count).ClearContents i = 2 For Each cust In dict.Keys outWs.Cells(i, "A").Value = cust outWs.Cells(i, "B").Value = dict(cust) i = i + 1 Next cust
End Sub

5.6. Kiểm Tra Dữ Liệu Đầu Vào Trùng Lặp Trong Form

Có thể bạn quan tâm: Cách Tạo Drop‑down List (select Option) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Khi người dùng nhập dữ liệu qua UserForm, ta có thể kiểm tra ngay:

Private Sub txtID_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Giả sử dữ liệu hiện tại trong cột A là danh sách ID đã tồn tại Dim rng As Range: Set rng = ThisWorkbook.Sheets("Data").Range("A2:A" & _ ThisWorkbook.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row) Dim cell As Range For Each cell In rng dict(cell.Value) = Empty Next cell If dict.Exists(Me.txtID.Value) Then MsgBox "Mã này đã tồn tại! Vui lòng nhập mã khác.", vbExclamation Cancel = True End If
End Sub

5.7. Đồng Bộ Dữ Liệu Giữa Hai Workbook

Sub SyncTwoWorkbooks() Dim wbSrc As Workbook, wbDst As Workbook Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim rngSrc As Range, cell As Range Set wbSrc = Workbooks.Open("C:\Data\Source.xlsx") Set wbDst = Workbooks.Open("C:\Data\Destination.xlsx") Set rngSrc = wbSrc.Sheets(1).Range("A2:B" & wbSrc.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row) ' Tạo lookup từ file nguồn For Each cell In rngSrc.Columns(1).Cells dict(cell.Value) = cell.Offset(0, 1).Value ' key = A, value = B Next cell ' Cập nhật hoặc thêm vào file đích Dim rngDst As Range, key As Variant Set rngDst = wbDst.Sheets(1).Range("A2:A" & wbDst.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row) For Each cell In rngDst key = cell.Value If dict.Exists(key) Then cell.Offset(0, 1).Value = dict(key) ' Cập nhật dict.Remove key ' Đánh dấu đã xử lý End If Next cell ' Thêm các mục còn lại (không tồn tại trong đích) Dim outRow As Long outRow = wbDst.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1 For Each key In dict.Keys wbDst.Sheets(1).Cells(outRow, "A").Value = key wbDst.Sheets(1).Cells(outRow, "B").Value = dict(key) outRow = outRow + 1 Next key wbSrc.Close False wbDst.Save wbDst.Close
End Sub

5.8. Tạo Đồ Thị Tự Động Từ Dữ Liệu Nhóm

Sau khi nhóm dữ liệu (phần 5.5), chúng ta có thể vẽ biểu đồ:

Sub ChartFromDictionary() Call GroupByCustomer ' Đảm bảo dữ liệu đã được tổng hợp ở Sheet "Summary" Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Summary") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim cht As ChartObject Set cht = ws.ChartObjects.Add(Left:=300, Top:=10, Width:=400, Height:=300) With cht.Chart .SetSourceData Source:=ws.Range("A1:B" & lastRow) .ChartType = xlColumnClustered .HasTitle = True .ChartTitle.Text = "Tổng doanh thu theo khách hàng" .Axes(xlCategory).HasTitle = True .Axes(xlCategory).AxisTitle.Text = "Mã khách hàng" .Axes(xlValue).HasTitle = True .Axes(xlValue).AxisTitle.Text = "Doanh thu" End With
End Sub

5.9. Sử Dụng Dictionary Để Kiểm Tra Định Dạng Email

Function IsValidEmail(email As String) As Boolean Dim regex As Object Set regex = CreateObject("VBScript.RegExp") regex.Pattern = "^\w\.-+@\w\.-+\.\w{2,4}$" IsValidEmail = regex.Test(email)
End Function Sub ValidateEmails() Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Emails") Dim rng As Range, cell As Range Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) For Each cell In rng If Len(cell.Value) > 0 Then If Not IsValidEmail(cell.Value) Then dict.Add cell.Row, cell.Value ' Lưu các email không hợp lệ End If End If Next cell ' Đánh dấu màu đỏ các ô lỗi Dim r As Variant For Each r In dict.Keys ws.Cells(r, "A").Interior.Color = vbRed Next r MsgBox dict.Count & " email không hợp lệ được đánh dấu.", vbInformation
End Sub

5.10. Đánh Dấu Dòng Trùng Lặp Trong File Log

Sub HighlightDuplicateLogLines() Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Log") Dim lastRow As Long, i As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow Dim txt As String: txt = ws.Cells(i, "A").Value If dict.Exists(txt) Then ws.Cells(i, "A").Interior.Color = RGB(255, 200, 200) ' Đánh dấu Else dict.Add txt, i End If Next i
End Sub

6. Các Mẹo Nâng Cao Khi Dùng Dictionary

6.1. Lưu Trữ Mảng Hoặc Collection Trong Item

Bạn có thể lưu một mảng các giá trị dưới một khóa duy nhất, hữu ích cho việc grouping.

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
' Ví dụ: nhóm các ngày tháng cho mỗi nhân viên
If dict.Exists(empID) Then dict(empID) = dict(empID) & "," & currentDate ' Cộng chuỗi
Else dict.Add empID, currentDate
End If

Sau này, dùng Split(dict(empID), ",") để lấy mảng.

6.2. Clone Để Tạo Bản Sao

Đôi khi bạn muốn giữ nguyên dữ liệu gốc và thao tác trên bản sao.

Dim original As Object, copyDict As Object
Set original = CreateObject("Scripting.Dictionary")
original.Add "A", 1
original.Add "B", 2 Set copyDict = original.Clone ' copyDict là một bản sao độc lập
copyDict("A") = 99 ' Không ảnh hưởng tới original

6.3. Sắp Xếp Keys Hoặc Items

Dictionary không có phương thức sắp xếp nội bộ, nhưng bạn có thể chuyển sang mảng, dùng Sort và ghi lại.

Dim keys() As Variant, i As Long
keys = dict.Keys
' Sắp xếp bằng hàm QuickSort tự viết hoặc Excel's WorksheetFunction.Sort
Call QuickSort(keys, LBound(keys), UBound(keys)) ' Ghi lại theo thứ tự sắp xếp
For i = LBound(keys) To UBound(keys) Debug.Print keys(i), dict(keys(i))
Next i

6.4. Xử Lý Lỗi Khi Add

Để tránh lỗi “Key already exists”, một mẫu chuẩn:

If Not dict.Exists(key) Then dict.Add key, value

Hoặc dùng On Error Resume Next (không khuyến khích vì ẩn lỗi).

6.5. Thay Thế For Each Khi Số Lượng Lớn

Khi Dictionary chứa hàng chục ngàn mục, vòng For Each vẫn nhanh, nhưng nếu cần truy cập ngẫu nhiên, bạn có thể dùng chỉ số:

Dim arrKeys As Variant: arrKeys = dict.Keys
Dim idx As Long
For idx = LBound(arrKeys) To UBound(arrKeys) Debug.Print arrKeys(idx), dict(arrKeys(idx))
Next idx

7. So Sánh Dictionary Với Các Cấu Trúc Dữ Liệu Khác

8. Xử Lý Các Vấn Đề Thường Gặp

8.1. Lỗi “Object required” Khi Tạo Dictionary

Nguyên nhân thường là thiếu thư viện hoặc cú pháp sai.

• Kiểm tra: CreateObject("Scripting.Dictionary") phải đúng.
• Nếu dùng Early Binding, chắc chắn đã tick Microsoft Scripting Runtime.

8.2. Lỗi “Key not found” Khi Truy Cập

• Đảm bảo khóa tồn tại: If dict.Exists(key) Then ….
• Kiểm tra CompareMode nếu có vấn đề về hoa/thường.

8.3. Bộ Nhớ (Memory) Khi Đóng File

Dictionary không tự giải phóng bộ nhớ khi macro kết thúc, vì vậy:

Set dict = Nothing ' Giải phóng ngay khi không còn dùng

8.4. Khi Dữ Liệu Có Kiểu Null hoặc Empty

• Dictionary coi Empty và Null là khóa hợp lệ. Tuy nhiên, việc sử dụng chúng làm khóa thường không thực tế.
• Thường lọc trước: If Len(Trim(cell.Value)) > 0 Then ….

9. Tích Hợp Dictionary Với Các Công Nghệ Khác

9.1. Đọc/Viết JSON

Với thư viện VBA‑JSON (JsonConverter), bạn có thể chuyển đổi JSON thành Dictionary và ngược lại.

Dim jsonStr As String, dict As Object
jsonStr = "{""name"":""John"",""age"":30}"
Set dict = JsonConverter.ParseJson(jsonStr) ' Trả về Dictionary Debug.Print dict("name") ' John

9.2. Sử Dụng Dictionary Để Tạo Pivot Table Nhanh

Bạn có thể tổng hợp dữ liệu bằng Dictionary rồi tạo PivotTable từ mảng kết quả, giảm thời gian tính toán so với Pivot tự động trên hàng triệu dòng.

9.3. Giao Tiếp Với ADO/SQL

Khi lấy dữ liệu từ cơ sở dữ liệu, bạn có thể lưu trữ lookup table trong Dictionary để giảm số lần truy vấn.

Dim rs As ADODB.Recordset, sql As String
sql = "SELECT ProductID, ProductName FROM Products"
Set rs = New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockReadOnly Dim prodDict As Object: Set prodDict = CreateObject("Scripting.Dictionary")
Do While Not rs.EOF prodDict.Add rs!ProductID, rs!ProductName rs.MoveNext
Loop

Sau này, khi đọc bảng bán hàng, chỉ cần prodDict(productID) để lấy tên sản phẩm mà không cần join.

10. Kết Luận

Scripting.Dictionary là một công cụ cực kỳ mạnh mẽ trong VBA Excel, cho phép bạn:

• Lưu trữ dữ liệu dưới dạng cặp khóa‑giá trị.
• Tra cứu nhanh, không phụ thuộc vào vị trí của dữ liệu.
• Quản lý dữ liệu linh hoạt: thêm, xóa, kiểm tra tồn tại, clone.
• Kết hợp với các công nghệ khác như JSON, ADO, UserForm để xây dựng giải pháp toàn diện.

Bằng cách nắm vững cách CreateObject(“Scripting.Dictionary”), thiết lập CompareMode, và áp dụng các phương thức như Add, Remove, Exists, Keys, Items, bạn sẽ có thể giải quyết hầu hết các bài toán liên quan tới lookup, đếm tần suất, loại bỏ trùng lặp, group by, và đồng bộ dữ liệu trong Excel một cách nhanh chóng và hiệu quả.

Hy vọng bài viết này đã cung cấp cho bạn một cẩm nang chi tiết từ cơ bản đến nâng cao, kèm theo 10 ví dụ thực tế để bạn có thể ngay lập tức áp dụng vào dự án của mình. Hãy thử tạo một Dictionary ngay hôm nay, và khám phá sức mạnh của nó trong việc tối ưu hoá macro VBA của bạn!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.