如何在VBA中移除数组重复值与空值并拼接结果

核心方案：利用Dictionary实现高效去重 #

Scripting.Dictionary的键具有唯一性，是VBA中处理去重需求的常用工具，同时可以轻松过滤空值，最后直接拼接成目标格式：

Sub ProcessArray()
    Dim strname As Variant
    Dim dict As Object
    Dim i As Integer
    Dim resultStr As String
    
    ' 初始化目标数组
    strname = Array("English", "science", "Social", "English", "Social", "science", "science", "Social", "English", "", "")
    
    ' 创建Dictionary实例
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 遍历数组：过滤空值 + 自动去重
    For i = LBound(strname) To UBound(strname)
        If strname(i) <> "" Then
            ' 仅当元素未在字典中时添加
            If Not dict.Exists(strname(i)) Then
                dict.Add strname(i), strname(i)
            End If
        End If
    Next i
    
    ' 拼接成指定格式的字符串
    resultStr = Join(dict.Keys, ";")
    
    ' 输出结果（可替换为写入单元格等操作）
    Debug.Print resultStr ' 输出：English;science;Social
    
    Set dict = Nothing
End Sub

补充方案：使用Collection实现 #

如果习惯用Collection，也可以通过捕获重复添加的错误来实现去重，只是拼接时需要额外处理末尾的分号：

Sub ProcessArrayWithCollection()
    Dim strname As Variant
    Dim col As Collection
    Dim i As Integer
    Dim resultStr As String
    Dim item As Variant
    
    strname = Array("English", "science", "Social", "English", "Social", "science", "science", "Social", "English", "", "")
    Set col = New Collection
    
    ' 捕获重复元素添加时的错误
    On Error Resume Next
    For i = LBound(strname) To UBound(strname)
        If strname(i) <> "" Then
            ' 利用Key的唯一性去重，重复添加会触发错误
            col.Add strname(i), Key:=strname(i)
        End If
    Next i
    On Error GoTo 0
    
    ' 拼接集合元素
    For Each item In col
        resultStr = resultStr & item & ";"
    Next item
    ' 移除最后多余的分号
    resultStr = Left(resultStr, Len(resultStr) - 1)
    
    Debug.Print resultStr
    Set col = Nothing
End Sub

原代码的问题分析 #

你之前的循环逻辑仅对比相邻元素，无法处理非相邻的重复值（比如数组中第一个和第四个English），且没有收集所有唯一元素的逻辑，自然无法得到目标结果。

内容的提问来源于stack exchange，提问作者Yes_par_row