About This Article
This article was created using an automated generation workflow powered by generative AI. We reviewed the Microsoft Learn Dictionary object specifications, re-reviewed existing samples, and revised the configuration so that input sheet columns are not deleted.Verification Status: 📘 Official Specifications Confirmed • Sample Safety Reviewed • Excel Actual Device Verification Not Performed
Dictionary is Convenient for Counting Duplicates in VBA — Aggregate Securely Without References
If you want to count how many times the same value appears in a single scan in Excel VBA, Scripting.Dictionary is very convenient. By using CreateObject("Scripting.Dictionary"), you can use it via Late Binding without needing to add a reference in the VBE.
Another important point this time is how results are output. Instead of overwriting or clearing an apparently empty column on the input sheet, results are output to a dedicated results sheet.
Concept of Dictionary
A Dictionary holds pairs of “keys” and “values”. In this case, cell strings will be the keys, and the occurrence counts will be the values.
Tokyo -> 3 Osaka -> 2 Fukuoka -> 1
If the same key appears, increment the count by 1; if it is the first time, add it with a count of 1.
Set dict = CreateObject("Scripting.Dictionary")
' Treat uppercase and lowercase letters as the same key.
' Set CompareMode before adding keys.
dict.CompareMode = vbTextCompare
For row = 2 To lastRow
key = Trim$(CStr(sourceWs.Cells(row, "A").Value2))
If Len(key) > 0 Then
If dict.Exists(key) Then
dict(key) = CLng(dict(key)) + 1
Else
dict.Add key, 1
End If
End If
Next row
Reasons for Using Late Binding
With Early Binding, you can declare the Scripting.Dictionary type directly, but it normally requires adding a reference to “Microsoft Scripting Runtime”. Because Late Binding uses Object and CreateObject, it reduces issues where references become missing and break functionality on distributed machines.
On the other hand, VBE IntelliSense and type checking become weaker. In educational materials or internal deployments, choose based on whether you want to “eliminate the need for reference settings” or “prioritize development-time type assistance.”
Using a Dedicated Sheet for the Output Destination
In the initial version, C:D column of the input sheet was ClearContents. However, if users had other data stored there, it would be erased.
Therefore, the current version creates an 重複集計結果 sheet and reuses only that sheet.
flowchart LR
A[入力シート A列] --> B[Scripting.Dictionary]
B --> C[キーごとに件数を加算]
C --> D[重複集計結果シート]
D --> E[値 / 件数]
Even “working code” must account for how existing data is handled to be truly production-ready. In this night’s generation run, the sample side was also fixed prior to article creation.
Determination Rules for This Sample
Targets row 2 and below
Leading and trailing spaces are removed with
Trim$Empty strings are not counted
Because of
vbTextCompare, alphabetical casing is not distinguishedValues are converted to strings using
CStrto serve as keys
For use cases where you want to strictly distinguish between numeric 123 and string "123", this approach is not suitable as-is. If you need to distinguish data types, you must change how keys are created.
