Excel VBA获取唯一值
Excel VBA获取唯一值
提问于 2018-07-04 05:57:27
我有两个数组: 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我想要的结果是删除了在DirHistoryArray中找到的所有值的DirCurrentyArray
回答 3
Stack Overflow用户
发布于 2018-07-04 13:05:30
我认为您可以根据自己的需求使用Dictionary来存储、比较和检索数组项。
你可以试试这样的东西...
Dim dict
Set dict = CreateObject("Scripting.Dictionary")
'Load DirCurrentArray into dictionary
For i = LBound(DirCurrentArray) To UBound(DirCurrentArray)
dict.Item(DirCurrentArray(i)) = ""
Next i
'Remove from dictionary if DirHistoryArray elements are found in dictionary
For i = LBound(DirHistoryArray) To UBound(DirHistoryArray)
If dict.exists(DirHistoryArray(i)) Then dict.Remove (DirHistoryArray(i))
Next i
'If dictionary is not empty then populate the DirCurrentArray with dictionary keys
If dict.Count Then
DirCurrentArray = dict.keys
MsgBox Join(DirCurrentArray, ", ")
Else
MsgBox "DirCurrentArray is empty."
End IfStack Overflow用户
发布于 2018-07-04 08:17:06
集合是用来查找唯一值的。我的代码首先将DirCurrentArray中的所有值添加到ArrayList中，然后从ArrayList中删除DirHistoryArray中的所有值。最后，它将ArrayList中的值赋给DirFinalArray。
Sub GetUniqueValuesFrom2Arrays()
Dim DirCurrentArray As Variant, DirHistoryArray As Variant, DirFinalArray, Key As Variant
Dim list As Object
Set list = CreateObject("System.Collections.ArrayList")
Dim i As Long, k As Variant, DirNewArray As Variant
DirCurrentArray = Array("A", "B", "C", 1, 2, 3, 4, 5)
DirHistoryArray = Array("C", "D", 1, 3, 5)
For Each Key In DirCurrentArray
list.Add Key
Next
For Each Key In DirHistoryArray
If list.Contains(Key) Then list.Remove Key
Next
DirFinalArray = list.ToArray()
MsgBox "DirCurrentArray: " & Join(DirCurrentArray, ",") & vbNewLine & _
"DirCurrentArray: " & Join(DirHistoryArray, ",") & vbNewLine & _
"DirFinalArray: " & Join(DirFinalArray, ",")
End SubStack Overflow用户
发布于 2018-07-04 13:29:52
看看这个
Option Base 1
Sub test()
Dim DirCurrentArray(5) As Integer
Dim DirHistoryArray(5) As Integer
Dim DirFinalArray() As Integer
DirCurrentArray(1) = 1
DirCurrentArray(2) = 4
DirCurrentArray(3) = 5
DirCurrentArray(4) = 1
DirCurrentArray(5) = 7
DirHistoryArray(1) = 1
DirHistoryArray(2) = 2
DirHistoryArray(3) = 1
DirHistoryArray(4) = 4
DirHistoryArray(5) = 1
Dim blnExist As Boolean
For Each i In DirCurrentArray
For Each j In DirHistoryArray
If i = j Then
blnExist = True
Exit For
End If
Next
If blnExist = False Then
finalcount = finalcount + 1
ReDim Preserve DirFinalArray(finalcount)
DirFinalArray(finalcount) = i
End If
blnExist = False
Next
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51163310
复制相关文章
相似问题