如何获得一组独特的值? [英] 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?

推荐答案

Use a Scripting.Dictionary (工具 -> 参考... -> Microsoft 脚本运行时):

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

在 VBScript 或 VBA 中，如果您更喜欢使用后期绑定(没有显式类型的变量):

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
    ...

如果在 Mac(没有 Microsoft Scripting Runtime)上运行 VBA，则有一个 可以直接替换字典.

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

一些例子:

• 从 VBA 中的过滤器中提取唯一值的集合
• 删除给定行中的重复条目
• 如何删除列表框中的重复项
• 如何获取唯一值从使用 VBscript 的值列表?
• VBScript:从数组中删除重复项
• VBA 中出现的单词
• 在基于用户的选择中查找所有不同的值 -Excel VBA

另一种选择(仅限 VBA)是使用 集合.有点尴尬，因为没有办法设置现有的键而不抛出错误，而且因为返回的数组必须手动创建:

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

这篇关于如何获得一组独特的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！