Fill a list box with unique items

[mdmackillop]

Re this item http://www.vbaexpress.com/forum/showthread.php?t=4107 I'm looking for a way to fill the listbox with unique items from Column C plus another item "All"

[Bob Phillips]

Re this item http://www.vbaexpress.com/forum/showthread.php?t=4107 I'm looking for a way to fill the listbox with unique items from Column C plus another item "All"

Malcolm,

I would use a dictionary object. Load that with an All item to start, then loop through C loading all items. Duplicates get ignored.

An alternative is to advance filter column C with Unique records and to a new place, just under an item of All.

I won't bother with the code, I am sure you can handle whichever, if any, you choose

[mdmackillop]

Hi Bob,
Never actually used the dictionary object, but it looks simple enough. Only problem is
with

Dim a, d 'Create a variable
Set d = CreateObject(Scripting.Dictionary)
For Each Cell In Range("C2:C" & [c65536].End(xlUp))
i = i + 1
d.Add i, Cell.Text
Next

I'm getting "Method or Data Member not found" with Dictionary highlighted.
I've added Scripting Runtime, and Script Control as references. Am I missing something?
Regards
Malcolm

[gsouza]

What is a dictonary object.

[Bob Phillips]

Do you know, I knew I had your name wrong, but I had that in my mind and went ahead with it. Should have known you had a good Scots name. Apologies for that.

Anyway, the error. It needs to be enclosed in quotes.

Set d = CreateObject("Scripting.Dictionary")

And preced it with On Error Resum Next in the real version, to ignore the duplicates.

[Bob Phillips]

What is a dictonary object.

http://msdn.microsoft.com/library/de...filesystem.asp

[Norie]

Why not use a Collection?

[Zack Barresse]

Why not use a Collection?

I believe the Dictionary Object is faster.

Another way (understandably debatable) is to use Advanced Filter, possibly coupled with an array.

[Tommy]

I like to use the dictonary object because it has a .Exist that returns a boolean, then based on the return, you do what you gotta do, saving the error checking for my other brain cramps . IMHO The collections are actually slower.

[MOS MASTER]

What is a dictonary object.

http://msdn.microsoft.com/library/de...dictionary.asp

[Bob Phillips]

I like to use the dictonary object because it has a .Exist that returns a boolean, then based on the return, you do what you gotta do, saving the error checking for my other brain cramps .

Sub TestCollection()
Dim col As Collection
    Set col = New Collection
    col.Add "Me", "xld"
    MsgBox IfExists(col, "xld")
End Sub

Public Function IfExists(col As Collection, ByVal sKey As String)
On Error GoTo NoSuchKey
If VarType(col.Item(sKey)) = vbObject Then
         ' force an error condition if key does not exist
     End If
IfExists = True
Exit Function
NoSuchKey:
     IfExists = False
End Function

Great thing about collections are that you have have a collection class for our custom objects.

[mdmackillop]

Anyway, the error. It needs to be enclosed in quotes.

Thanks Bob,
I foolishly copied the code from Excel Help, thinking that it would be correct!
Works well.
Regards
Malcolm

[brettdj]

I like to use the dictonary object because it has a .Exist that returns a boolean, then based on the return, you do what you gotta do, saving the error checking for my other brain cramps . IMHO The collections are actually slower.

Bingo

Malcolm, I use both Dictionary and Collection examples in http://www.vbaexpress.com/kb/getarticle.php?kb_id=67

Cheers

Dave

[mdmackillop]

Hi Dave,
Your entry on the KB list is as below, so unfortunately looking at this list would not get me to your entry. Are we needing to set Keywords into the description to make the KB more searchable.
ExcelSimulate a Poker GameThis code inserts a new worksheet and deals a 5-Card Poker hand to 10 players. The code requires Excel 2000 or higher.

[brettdj]

hmm - don't keywords in the KB Description get used for searching?

[sheeeng]

Bingo

Malcolm, I use both Dictionary and Collection examples in http://www.vbaexpress.com/kb/getarticle.php?kb_id=67

Cheers

Dave

That is a wonderful tips.
Thx.

[XL-Dennis]

The only objection I have with the Scripting approach is that it may cause unnessecary overhead compared with the built-in Collection. For small solution this is not an issue while for large application it may be.

In my experience many large worldwide clients do not accept the use of this library due to security. Within some companies I've been working with the library is not available at all.

Looping through a range is slower then reading the range values into a variant variable and then populate the choosen collection.

Kind regards,
Dennis

[mdmackillop]

Thanks all!

[Zack Barresse]

The site search engine and the KB search engine are completely different, and at the moment do not cross-reference each other. The KB Search engine is custom scripted by smozgur.