vba 加载只有唯一值的数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5382431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loading an array with only unique values
提问by Jose Quervo
I have a range I am looping through in VBA:
我有一个在 VBA 中循环的范围：
For Lrow = Firstrow To Lastrow Step 1
With .Cells(Lrow, "E")
If Not IsError(.Value) Then
End If
End With
Next Lrow
Within that if statement I need to load an array with each value only once
在那个 if 语句中，我只需要加载一个包含每个值的数组一次
MB-NMB-ILA
MB-NMB-ILA
MB-NMB-STP
MB-NMB-STP
MB-NMB-WAS
MB-NMB-WAS
MB-NMB-WAS
MB-NMB-ILA
MB-NMB-ILA
MB-NMB-STP
MB-NMB-STP
MB-NMB-WAS
MB-NMB-WAS
MB-NMB-WAS
So for the array I only want MB-NMB-ILA, MB-NMB-STP, and MB-NMB-WAS
所以对于阵列，我只想要 MB-NMB-ILA、MB-NMB-STP 和 MB-NMB-WAS
Can anyone help me out, my brain isn't working right on a Monday! Thanks
谁能帮帮我，我的大脑在星期一不能正常工作！谢谢
回答by DontFretBrett
You could use filter to test if something exists in the array.
您可以使用过滤器来测试数组中是否存在某些内容。
Dim arr As Variant: arr = Array("test1", "test2", "test3")
If UBound(Filter(arr, "blah")) > -1 Then
Debug.Print "it is in the array"
Else
Debug.Print "it's not in the array"
End If
You could also use a collection and write a sub to add only unique items to the collection
您还可以使用一个集合并编写一个子项以仅将唯一项添加到集合中
Dim col As New Collection
Sub addIfUnique(sAdd As String)
Dim bAdd As Boolean: bAdd = True
If col.Count > 0 Then
Dim iCol As Integer
For iCol = 1 To col.Count
If LCase(col(iCol)) = LCase(sAdd) Then
bAdd = False
Exit For
End If
Next iCol
End If
If bAdd Then col.Add sAdd
End Sub
Private Sub Command1_Click()
Dim a As Integer
Dim b As Integer
For a = 1 To 10
addIfUnique "item " & a
For b = 1 To 10
addIfUnique "item " & b
Next b
Next a
For a = 1 To col.Count
Debug.Print col(a)
Next a
End Sub
回答by Alex P
Suppose I have the following in cell A1 to A5 and want an array of unique values i.e. {a,b,c,d}
假设我在单元格 A1 到 A5 中有以下内容并且想要一个唯一值数组，即 {a,b,c,d}
A
1 "a"
2 "b"
3 "c"
4 "c"
5 "d"
The follow two pieces of code will help achieve this:
以下两段代码将有助于实现这一点：
CreateUniqueArray- get val from each cell and add to array if not already in array
CreateUniqueArray- 从每个单元格中获取 val 并添加到数组中（如果不在数组中）
IsInArray- utility function to check if value in array by performing simple loop
IsInArray- 通过执行简单循环来检查数组中是否有值的实用函数
I have to say that this is the brute force way and would welcome any improvements...
我不得不说这是蛮力方式，欢迎任何改进......
Sub Test()
Dim firstRow As Integer, lastRow As Integer, cnt As Integer, iCell As Integer
Dim myArray()
cnt = 0
firstRow = 1
lastRow = 10
For iCell = firstRow To lastRow
If Not IsInArray(myArray, Cells(iCell, 1)) Then
ReDim Preserve myArray(cnt)
myArray(cnt) = Cells(iCell, 1)
cnt = cnt + 1
End If
Next iCell
End Sub
Function IsInArray(myArray As Variant, val As String) As Boolean
Dim i As Integer, found As Boolean
found = False
If Not Len(Join(myArray)) > 0 Then
found = False
Else
For i = 0 To UBound(myArray)
If myArray(i) = val Then
found = True
End If
Next i
End If
IsInArray = found
End Function
