PDA

View Full Version : [SOLVED] Fill a list box with unique items



mdmackillop
07-15-2005, 09:52 AM
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
07-15-2005, 10:01 AM
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
07-15-2005, 11:33 AM
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
07-15-2005, 11:37 AM
What is a dictonary object.

Bob Phillips
07-15-2005, 01:58 PM
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
07-15-2005, 01:59 PM
What is a dictonary object.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjfilesystem.asp

Norie
07-15-2005, 02:04 PM
Why not use a Collection?

Zack Barresse
07-15-2005, 02:17 PM
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
07-15-2005, 02:24 PM
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
07-15-2005, 02:25 PM
What is a dictonary object.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjdictionary.asp

Bob Phillips
07-15-2005, 03:04 PM
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
07-15-2005, 04:32 PM
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
07-16-2005, 01:24 AM
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
07-16-2005, 01:59 AM
Hi Dave,
Your entry on the KB list (http://www.vbaexpress.com/kb/kblist.php) 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 Game (http://www.vbaexpress.com/kb/getarticle.php?kb_id=67)This code inserts a new worksheet and deals a 5-Card Poker hand to 10 players. The code requires Excel 2000 or higher.

brettdj
07-16-2005, 03:15 AM
hmm - don't keywords in the KB Description get used for searching?

sheeeng
07-16-2005, 08:42 AM
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
07-16-2005, 10:03 AM
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
07-17-2005, 05:55 AM
Thanks all!:clap:

Zack Barresse
07-18-2005, 12:17 AM
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.