Excel VBA 获取唯一值

Question

我有 2 个数组:DirCurrentArray 和 DirHistoryArray，但我似乎无法从 DirHistoryArray 获取 DirCurrentArray 中唯一的值

Dim DirCurrentArray As String
Dim DirHistoryArray As Variant

'Gets Filenames into Array
Do While xFile <> ""
    DirCurrentArray(fileCount) = xFile
    xFile = Dir$
    fileCount = fileCount + 1
Loop

For Each i In DirCurrentArray
        For Each j In DirHistoryArray
            If i = j Then
                finalCount = finalCount + 1
                DirFinalArray(finalCount) = i
            End If
        Next j
    Next i

我想要的结果是 DirCurrentyArray，其中删除了 DirHistoryArray 中找到的任何值

Answer 1

此代码的有效性在一定程度上取决于您正在比较的数据的性质，因为基于文本的值可能会在部分匹配上产生误报，例如通配符搜索。即使是 1 也会在 11 或 15 等中找到过滤器匹配。我已经使用工作表的 Match 函数添加了“整个单词”匹配作为替代方案。

Option Explicit

Sub ytrte()
    Dim DirCurrentArray As Variant, DirHistoryArray As Variant
    Dim i As Long, k As Variant, DirNewArray As Variant

    DirCurrentArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 0)
    DirHistoryArray = Array(3, 4, 5, 6, 7, 8, 11)

    ReDim DirNewArray(0)
    i = 0

    ' 'wildcard' matching
    For Each k In DirCurrentArray
        If UBound(Filter(DirHistoryArray, k, True, vbBinaryCompare)) < 0 Then
            ReDim Preserve DirNewArray(i)
            DirNewArray(i) = k
            i = i + 1
        End If
    Next k

    If Not IsEmpty(DirNewArray(LBound(DirNewArray))) Then
        For i = LBound(DirNewArray) To UBound(DirNewArray)
            Debug.Print DirNewArray(i)
        Next i
    End If

'contents of DirNewArray
 2 
 9 
 0 

    ReDim DirNewArray(0)
    i = 0

    ' 'whole word' matching
    For Each k In DirCurrentArray
        If IsError(Application.Match(k, DirHistoryArray, 0)) Then
            ReDim Preserve DirNewArray(i)
            DirNewArray(i) = k
            i = i + 1
        End If
    Next k

    If Not IsEmpty(DirNewArray(LBound(DirNewArray))) Then
        For i = LBound(DirNewArray) To UBound(DirNewArray)
            Debug.Print DirNewArray(i)
        Next i
    End If
'contents of DirNewArray
 1 
 2 
 9 
 0 

End Sub

调整循环以填充文件名。

Dim DirCurrentArray() As Variant
Dim fileCount As long
...
'Gets Filenames into Array
fileCount = 0
Do While xFile <> ""
    redim preserve DirCurrentArray(fileCount)
    DirCurrentArray(fileCount) = xFile
    fileCount = fileCount + 1
    xFile = Dir$
Loop