vba: get unique values from array

📝 VBA: Get Unique Values from Array 💡

Have you ever found yourself in a situation where you need to extract unique values from a one-dimensional array in VBA? Maybe you're dealing with some pesky duplicates and just want to get rid of them. In this blog post, we'll explore some common issues related to this problem and provide you with easy and practical solutions. So, let's dive in and unravel the mystery of getting unique values from an array in VBA!

The Problem 🧩

The first question that comes to mind is whether there's a built-in functionality in VBA to tackle this problem. Unfortunately, VBA doesn't provide a straightforward way to directly obtain unique values from an array. However, fear not! There are alternative methods that we can employ to achieve our desired outcome.

Solution 1: Using a Dictionary 📚

One effective approach is to utilize the Dictionary object from the Microsoft Scripting Runtime library. This powerful tool allows us to store unique keys and corresponding values. By taking advantage of the unique key property, we can easily extract the distinct elements from an array.

Here's an example of how you can implement this solution:

Sub GetUniqueValuesFromArray(arr() As Variant)
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim element As Variant
    
    For Each element In arr
        dict(element) = 1   ' Assigning a value is optional, 1 is just a placeholder
    Next element
    
    Dim uniqueValues() As Variant
    uniqueValues = dict.keys
    
    ' Now you can do whatever you want with the unique values array
    ' ...your code here...
    
End Sub

By iterating through the array, we add each element as a key to the dictionary. The duplicates are automatically eliminated since the dictionary only allows unique keys. Finally, by accessing the keys property, we obtain an array of unique values.

Solution 2: Using a Collection 🗃️

An alternative method to obtain unique values is by using a Collection object. Although not as robust as the Dictionary, a Collection can still serve the purpose of getting rid of duplicates in an array.

Here's an example of how you can leverage a Collection to solve this problem:

Sub GetUniqueValuesFromArray(arr() As Variant)
    Dim col As Collection
    Set col = New Collection
    
    Dim element As Variant
    
    On Error Resume Next
    For Each element In arr
        col.Add element, CStr(element)  ' Adding duplicates will throw an error and be skipped
    Next element
    On Error GoTo 0
    
    Dim uniqueValues() As Variant
    
    ReDim uniqueValues(1 To col.Count)
    For i = 1 To col.Count
        uniqueValues(i) = col(i)
    Next i
    
    ' Now you can do whatever you want with the unique values array
    ' ...your code here...
    
End Sub

In this solution, we try to add each element to the Collection. If a duplicate is encountered, an error is thrown, which we promptly skip using the On Error Resume Next statement. Finally, we transfer the unique values from the Collection to an array for further processing.

Your Turn! ✨

Now that you have two practical solutions for obtaining unique values from an array in VBA, it's time to put them into action! Feel free to use whichever method suits your needs best. Whether you choose to use the Dictionary or the Collection, make sure to share your experience in the comments below.

Tell us about your favorite ways of handling duplicates in arrays or any struggles you've faced when working with VBA. Your insights can help others and spark meaningful discussions within our tech community!

🙌 Share your thoughts and experiences in the comments section below! 🎉