Yes. VBA arrays can contain duplicate values; VBA does not automatically prevent or remove them. To find repeats, compare elements or track values already seen; to remove repeats, build a new array containing each value once.
What Duplicate Values Mean in a VBA Array
An array is an ordered group of elements, and each position can hold a value that also appears at another position. For example, an array containing "Red", "Blue", "Red" has three elements but only two distinct values. The repeated value does not cause an error, and VBA does not treat it as a special condition.
It is useful to distinguish two meanings of “duplicate.” A duplicated value is a value that occurs more than once; a duplicate occurrence is an extra copy beyond the first. In the example, there is one distinct duplicated value, Red, and one extra occurrence of it. When reporting or removing repeats, decide which meaning matters to your task.
Arrays may be fixed-size or dynamic, and their lower bound can vary. The built-in Array function usually produces a zero-based Variant array, while a declared array may use another lower bound. Use LBound and UBound rather than assuming that the first element is at index zero.
Check an Array with Nested Loops
For a small array, nested loops are the most direct approach: compare each element with the elements after it. This checks every pair once and stops at the first match. The following example uses a one-dimensional array of ordinary scalar values and displays the first repeated value it finds.
Before running any macro, save a copy of the workbook; VBA macros cannot be undone with Ctrl+Z. This read-only example does not change the array, but keeping a copy is a sensible precaution when working with VBA.
Sub FindFirstDuplicate()
Dim values As Variant
Dim i As Long
Dim j As Long
' Array returns a zero-based Variant array.
values = Array("Red", "Blue", "Red", "Green")
For i = LBound(values) To UBound(values) - 1
For j = i + 1 To UBound(values)
If values(i) = values(j) Then
MsgBox "Duplicate found: " & CStr(values(i))
Exit Sub
End If
Next j
Next i
MsgBox "No duplicate values found."
End Sub
For this input, the result is “Duplicate found: Red.” The outer loop starts at the first valid index, and the inner loop starts one position later, so an element is never compared with itself. An array with one element simply has no pair to compare. The example assumes the array is initialized, one-dimensional, and contains values that can be compared with =; it is not designed for arrays containing objects, Null, or error values.
Use a Dictionary for Faster Lookups
For a larger collection, checking all pairs can take much longer because the number of comparisons grows roughly with the square of the number of elements. A dictionary provides a convenient way to remember values already encountered: for each item, check whether its key is present, then add it if it is not. The following example is specifically for text values and uses case-insensitive matching.
Save a copy of the workbook before running a macro. This code uses late binding, so it does not require adding a reference in the VBA editor, but Scripting.Dictionary is a Windows scripting component and may not be available in Excel for Mac.
Sub FindRepeatedText()
Dim values As Variant
Dim seen As Object
Dim i As Long
Dim item As String
values = Array("Maple", "Oak", "maple", "Birch")
Set seen = CreateObject("Scripting.Dictionary")
seen.CompareMode = vbTextCompare
For i = LBound(values) To UBound(values)
item = CStr(values(i))
If seen.Exists(item) Then
MsgBox "Repeated text: " & item
Exit Sub
End If
seen.Add item, True
Next i
MsgBox "No repeated text found."
End Sub
The example reports a repeat because “Maple” and “maple” are treated as the same key. Set CompareMode before adding any keys; changing it after the dictionary contains entries can raise an error. For case-sensitive matching, use vbBinaryCompare. Because this code converts each value to text with CStr, it is intended for text data, not for distinguishing numbers from numeric-looking strings.
Remove Repeats While Keeping the First Occurrence
Removing duplicates usually means creating a new array rather than deleting positions from the original. The function below preserves the order of first appearances: given Red, Blue, Red, Green, Blue, it returns Red, Blue, Green. It uses nested comparisons so it does not depend on the Windows dictionary component, making the approach more portable to Mac VBA.
Pass an initialized, one-dimensional array containing comparable scalar values. The returned value is a zero-based Variant array. This straightforward method is suitable for modest arrays; for very large arrays, repeated comparisons and repeated resizing can be inefficient.
Function UniqueValues(ByVal source As Variant) As Variant
Dim result() As Variant
Dim i As Long
Dim j As Long
Dim count As Long
Dim isNew As Boolean
ReDim result(0 To 0)
count = 0
For i = LBound(source) To UBound(source)
isNew = True
For j = 0 To count - 1
If source(i) = result(j) Then
isNew = False
Exit For
End If
Next j
If isNew Then
If count > 0 Then ReDim Preserve result(0 To count)
result(count) = source(i)
count = count + 1
End If
Next i
UniqueValues = result
End Function
This function returns a new array and leaves the source array unchanged. It keeps the first matching value and discards later matches; it does not sort the results. If you need sorted output, sort the unique results separately. If the input may contain Null, Error values, objects, or values with special comparison requirements, add explicit handling rather than relying on the simple equality test.
Choose a Matching Rule Before Comparing
“Duplicate” depends on what counts as equal for your data. For text, should “North” and “north” match? Should “A ” with a trailing space match “A”? For mixed data, should the number 1 match the text value “1”? VBA comparisons can coerce values, and string comparison behavior can depend on the module’s Option Compare setting. The default is binary comparison; a module using Option Compare Text compares strings without distinguishing letter case.
For predictable results, normalize values deliberately before comparing. For example, text-only input can be trimmed and converted to one case before it is checked. Do this only if those differences are irrelevant to the data: trimming may erase meaningful spaces, and converting types can make distinct inputs appear identical. A dictionary’s CompareMode controls its text-key comparison, but it does not decide how a separate nested-loop comparison works.
- For case-sensitive text, use binary string comparison.
- For case-insensitive text, use a text comparison rule consistently.
- For mixed types, decide whether values such as numeric 1 and text “1” should be treated as equivalent.
- For dates, errors, Nulls, or objects, define and test a specific comparison rule instead of assuming ordinary
=is sufficient.
Common Problems and Edge Cases
A frequent mistake is hard-coding array bounds. An array may start at index 1, at index 0, or at another declared lower bound, so loops that assume a particular starting index can skip elements or access an invalid position. LBound and UBound avoid that issue for initialized, one-dimensional arrays. A multidimensional array requires bounds for the relevant dimension and a loop for each dimension.
Another issue is passing an uninitialized dynamic array. Calling LBound or UBound on one can raise an error, so the examples above assume an allocated array with at least one element. If a procedure must accept empty or uninitialized arrays, check for that condition before reading bounds and define what its result should be.
Also avoid treating every array as a simple list of comparable values. A Variant array may contain values of different types; Null does not behave like an ordinary value in a Boolean comparison, and Error values need dedicated handling. Arrays of objects require identity or property-based comparison, not a generic value comparison. Test the chosen routine with representative data, including blanks, case differences, and any special types used in the workbook.
Arrays Versus Worksheet Duplicate Tools
VBA array checks and Excel’s worksheet duplicate tools solve related but different problems. An array routine checks values currently held in memory by VBA, which is useful while importing, transforming, or validating data in a macro. Excel’s worksheet commands operate on cells. In Microsoft Excel for Microsoft 365 on Windows, the Remove Duplicates command is on the Data tab; it changes the selected range, so copy the data first if the original must be retained.
For formulas in a worksheet, functions such as UNIQUE can return distinct values in supported Excel versions, including Microsoft 365. That does not automatically deduplicate a VBA array: worksheet formulas and VBA code are separate tools. Choose the method that matches where the data lives and whether the source should be changed. Excel for the web does not run VBA macros, so use desktop Excel to execute these procedures.
Frequently Asked Questions
Can a VBA array contain duplicate values?
Yes. Arrays can hold the same value at multiple indexes. VBA allows those repetitions unless your own code checks for them.
How do I find duplicate values in a VBA array?
For a small array, compare each element with later elements using nested loops. For a larger text array on Windows, a Scripting.Dictionary can track values already seen.
How do I remove duplicates but keep the original order?
Build a new array and append an item only if it has not appeared earlier. This keeps the first occurrence and preserves the order of the unique values.
Does VBA compare text duplicates without regard to capitalization?
Not always. String comparisons depend on the comparison rule; the default is binary, while Option Compare Text makes string comparisons case-insensitive in that module. A dictionary has its own CompareMode setting.
Can I use Scripting.Dictionary in Excel for Mac?
The Windows example relies on a Windows scripting component and may not work on Mac. Nested loops avoid that dependency and are a practical alternative for arrays that are not too large.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm Option Button: Setup and VBA GuideAn Excel UserForm option button (also called a radio button) lets a user choose one item from a…
- Excel VBA: Open a Worksheet by Name or IndexTo open a worksheet in Excel VBA, activate it with a statement such as ThisWorkbook.Worksheets("Data").Activate; replace Data with…
- VBA GoTo: Syntax, Examples, and Safer AlternativesIn VBA, GoTo is a statement, not a function: it transfers execution to a labeled line in the…
- VBA Create New Workbook With Name: A Practical GuideIn Excel VBA, create a workbook with Workbooks.Add, then give it a lasting name and location with SaveAs.…
- Excel VBA: How to Find the Next Empty Cell in a ColumnTo find the next empty cell below the last entry in an Excel column, use VBA’s End(xlUp) navigation…
- VBA Pivot Table Delete: What to Remove and HowYes. In Excel for Microsoft 365 on Windows, remove a PivotTable report with VBA by clearing its full…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.