Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

vba - How can I get a unique set of values?

Given some sort of collection of values (an array or a collection of some kind), how can I generate a set of distinct values?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use a Scripting.Dictionary (Tools -> References... -> Microsoft Scripting Runtime):

Function Unique(values As Variant) As Variant()
    'Put all the values as keys into a dictionary
    Dim dict As New Dictionary
    Dim val As Variant
    For Each val In values
        dict(val) = 1
    Next
    Unique = dict.Keys 'This cannot be done with a Collection, which doesn't expose its keys
End Function

In VBScript, or in VBA if you prefer using late binding (variables without explicit types):

Function Unique(values)
    Dim dict, val
    Set dict = CreateObject("Scripting.Dictionary")
    For Each val In values
    ...

If running VBA on a Mac (which doesn't have the Microsoft Scripting Runtime), there is a drop-in replacement for Dictionary available.

Some examples:


Another option (VBA only) is to use a Collection. It's a little more awkward, because there is no way to set an existing key without an error being thrown, and because the returned array has to be created manually:

Function Unique(values As Variant) As Variant()
    Dim col As New Collection, val As Variant, i As Integer
    For Each val In values
        TryAdd col, val, val
    Next
    Dim ret() As Variant
    Redim ret(col.Count - 1)
    For i = 0 To col.Count-1
        ret(i) = col(i+1)
    Next
    Unique = ret
End Function

Sub TryAdd(col As Collection, item As Variant, key As String)
    On Error Resume Next
    col.Add(item, key)
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...