只有组合框 (VBA) 中的唯一记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7840343/
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
Only unique records in a Combobox (VBA)
提问by Andrei Ion
I have a combobox where I add some stuff from an Excel sheet with a bunch of stuff. I want only unique records and I want them to be updated when I switch to this page. For that I used the following code:
我有一个组合框，我在其中从 Excel 工作表中添加了一些东西和一堆东西。我只想要唯一的记录，并且希望在切换到此页面时更新它们。为此，我使用了以下代码：
Private Sub MultiPage1_Change()
Dim Rand As Long
Dim ws As Worksheet
Set ws = Worksheets("BD_IR")
Dim i As Long
Rand = 3
Do While ws.Cells(Rand, 3).Value <> "" And Rand < 65536
If Me.repereche.ListCount <> 0 Then
For i = 0 To (Me.repereche.ListCount)
If Me.repereche.List(i, 0) <> Mid(ws.Cells(Rand, 3).Value, 4, 10) Then
Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
End If
Next i
ElseIf Me.repereche.ListCount = 0 Then
Me.Controls("repereche").AddItem Mid(ws.Cells(Rand, 3).Value, 4, 10)
End If
Rand = Rand + 1
Loop
The problem with this code (and I don't know where the problem is?) is that whenever I change the page and I come back to the page where this combobox is... it adds more (not unique) and more items. Where am I wrong?
这段代码的问题（我不知道问题出在哪里？）是，每当我更改页面并返回到此组合框所在的页面时……它添加了更多（不是唯一的）和更多项目。我哪里错了？
回答by Reafidy
Try this code:
试试这个代码：
Dim ws As Worksheet
Dim rCell As Range
Set ws = Worksheets("BD_IR")
'//Clear combobox
repereche.Clear
With CreateObject("Scripting.Dictionary")
For Each rCell In ws.Range("C3", ws.Cells(Rows.Count, "C").End(xlUp))
If Not .exists(rCell.Value) Then
.Add rCell.Value, Nothing
End If
Next rCell
repereche.List = .keys
End With
I prefer this over a collection as you can check if the value exists in the dictionary rather than using on error and add the entire collection to the combobox at once.
我更喜欢这个而不是集合，因为您可以检查字典中是否存在该值，而不是使用错误并将整个集合立即添加到组合框。
