VBA Dictionary Key Order Fixed?

[ChloeRadshaw]

If I want to store a unknown number of elements in Java I d use a LinkedList.

I ve been toying with Dictionary's and I think people can do the following:

Dim dict As New Dictionary
dict.add(abc, "")
dict.add(def, "")
dict.add(hij, "")

Dim vARray() As Variant
vARray = dict.keys

NOW MY QUESTION IS:

Is this a good way of doing this? I see two problems:

1) That the value is defaulted to an empty string
2) That the order of the keys returned is not the same as how they were added....

Can anyone comment on this please?

[ChloeRadshaw]

Is there a difference between doing this?

Dim vARray() As Variant
and
Dim vARray As Variant ?

Both of them are empty arrays and will be redimmed.......

Am I missing somthing obvious here>

[Kenneth Hobs]

It is best to ask one topic question per thread.

Regarding your dictionary question, you had syntax problems. I could not duplicate your problem.
Sub TestDict()
'Add, Tools > Reference > MicroSoft Scripting Runtime, scrrun.dll
Dim dict As New Dictionary, vARray() As Variant
dict.Add "abc", ""
dict.Add "def", ""
dict.Add "hij", ""
vARray = dict.keys
MsgBox Join(vARray, vbCrLf)
End Sub

Regarding your ()'s question. If you dim a variant variable with ()'s, you can reference the variable with or without ()'s. However, should you not dim with ()'s, using ()'s to reference the variable in the body of the code will fail at runtime with error 9, subscript out of range. I like to be literal and use ()'s generally.

cheers

[ChloeRadshaw]

It is best to ask one topic question per thread.

Regarding your dictionary question, you had syntax problems. I could not duplicate your problem.
Sub TestDict()
'Add, Tools > Reference > MicroSoft Scripting Runtime, scrrun.dll
Dim dict As New Dictionary, vARray() As Variant
dict.Add "abc", ""
dict.Add "def", ""
dict.Add "hij", ""
vARray = dict.keys
MsgBox Join(vARray, vbCrLf)
End Sub

Regarding your ()'s question. If you dim a variant variable with ()'s, you can reference the variable with or without ()'s. However, should you not dim with ()'s, using ()'s to reference the variable in the body of the code will fail at runtime with error 9, subscript out of range. I like to be literal and use ()'s generally.

cheers

Thank you - Thats very helpful - What i am asking is are the keys fixed in so much as the order that you put them in will e the order you get them out again?

FWIW - In java and perl assocaitaive arrays (aka Dictionarys) make no such guarantee - In your experience if you put in 5 keys and then get them out IS ORDER PRESERVED?

Thanks for point 2 - That makes sense

[Kenneth Hobs]

Yes, order is maintained. You can iterate the collection just as you would an array using For Each or a regular For loop.
e.g.
Sub TestDict()
'Add, Tools > Reference > MicroSoft Scripting Runtime, scrrun.dll
Dim dict As New Dictionary, vARray() As Variant
dict.Add 1, "One"
dict.Add 2, "Two"
dict.Add 22, "Twenty-Two"
dict.Add 3, "Three"
vARray() = dict.Keys
MsgBox Join(vARray, vbCrLf)

Dim i As Integer
For i = 0 To dict.Count - 1
MsgBox "Item " & i & " = " & dict.Items(i), , "Key " & i & " = " & dict.Keys(i)
Next i

' List all key value pairs
Dim Key As Variant
For Each Key In dict
MsgBox "Key=" & Key & " Value=" & dict.Item(Key)
Next Key

dict.Remove (2)
vARray() = dict.Keys
MsgBox Join(vARray, vbCrLf)

dict.Add 2, "Two"
MsgBox Join(dict.Keys, vbCrLf)
End Sub

[malik641]

Hey ChloeRadshaw,

Avoid using "" in your code when possible if you want to refer to an empty string (definitely use "" if you need to make a " inside a string like myString="here is a quote """" in the string").

Use the VB constant vbNullString. It is equivalent to "" but it is much, much faster to process because each string literal found in your code causes memory allocation per string (10 bytes for the "" string). When using vbNullString, everytime the code sees {dict.Add "abc", vbNullString} it will not allocate memory for the vbNullString, saving you time.

Obviously, if the list isn't long, you won't see much of a performance gain. But when you are dealing with a lot of strings, the performance gain will be clear.

Sub TestDict2()
'Add, Tools > Reference > MicroSoft Scripting Runtime, scrrun.dll
Dim dict As New Dictionary, vARray() As Variant
dict.Add "abc", vbNullString
dict.Add "def", vbNullString
dict.Add "hij", vbNullString
vARray = dict.Keys
MsgBox Join(vARray, vbCrLf)
End Sub

[ChloeRadshaw]

Hey ChloeRadshaw,

Avoid using "" in your code when possible if you want to refer to an empty string (definitely use "" if you need to make a " inside a string like myString="here is a quote """" in the string").

Use the VB constant vbNullString. It is equivalent to "" but it is much, much faster to process because each string literal found in your code causes memory allocation per string (10 bytes for the "" string). When using vbNullString, everytime the code sees {dict.Add "abc", vbNullString} it will not allocate memory for the vbNullString, saving you time.

Obviously, if the list isn't long, you won't see much of a performance gain. But when you are dealing with a lot of strings, the performance gain will be clear.

Sub TestDict2()
'Add, Tools > Reference > MicroSoft Scripting Runtime, scrrun.dll
Dim dict As New Dictionary, vARray() As Variant
dict.Add "abc", vbNullString
dict.Add "def", vbNullString
dict.Add "hij", vbNullString
vARray = dict.Keys
MsgBox Join(vARray, vbCrLf)
End Sub

Thanks malik641 - Thats very helpful!!

Can I just ask as a final question - Do you use Dictionarys like this yourself?

If you neeed to store an unbounded number of items - Do you use a dict with a vbnullstring or is there an alternative datstructure which allows this?

Thanks again

[malik641]

You're welcome.

Yes, I do use Dictionaries every once in a while. Most of the time I use them for creating a list of unique items. Here's an example of deleting duplicate values in a column (clearing contents):

Public Sub DeleteDupsContentOnly(ByRef ws As Excel.Worksheet, ByVal iColumn As Long, ByVal iStartRow As Long)
' this code will loop through a column of data and
' replace a column with a unique list of the values
' NOTE: this code will NOT delete the entire row for each duplicate

Dim dict As Object
Dim iLastRow As Long
Dim iRow As Long
Dim iNewRowLength As Long

' get the last row of data from the column iColumn
iLastRow = ws.Cells(ws.Rows.Count, iColumn).End(xlUp).Row
If iLastRow <= iStartRow Then Exit Sub

' the Dictionary object is great for storing a unique collection of items
Set dict = CreateObject("Scripting.Dictionary")

' loop through all the cells, collecting values only once
' to create a unique list
For iRow = iStartRow To iLastRow
If Not dict.Exists(ws.Cells(iRow, iColumn).Value) Then
dict.Add ws.Cells(iRow, iColumn).Value, Nothing
iNewRowLength = iNewRowLength + 1
End If
Next

' turn screen refreshing off (excel only)
Application.ScreenUpdating = False

ws.Range(ws.Cells(iStartRow, iColumn), ws.Cells(iLastRow, iColumn)).ClearContents
ws.Range(ws.Cells(iStartRow, iColumn), ws.Cells(iStartRow + iNewRowLength - 1, iColumn)).Value = WorksheetFunction.Transpose(dict.Keys)

' turn screen refreshing back on
Application.ScreenUpdating = True
End Sub

Private Sub driverProgram()
Dim iColumn As Long, iStartRow As Long
iStartRow = 4
For iColumn = 3 To 53
Call DeleteDupsContentOnly(ActiveSheet, iColumn, iStartRow)
Next
End Sub
In this example, notice I use the Nothing keyword instead of vbNullString. I usually use Nothing if I don't need to associate the Keys with a Value or vice versa. I'm not sure how much of a performance gain there is (if any), but using Nothing was how I first learned about the dictionary object in this thread (http://www.vbaexpress.com/forum/showthread.php?t=6381).

It looks like you have a good understanding of the Dictionary Object, but just incase here's some documentation (http://msdn.microsoft.com/en-us/library/x4k5wbx4.aspx).

You could also do this with Arrays and I believe you can also do this with a Collection object, but the Collection object doesn't have an Exists method which is inconvenient. Arrays are manageable to use, but you have to worry about the size of the array, create your own Exists method, and if you don't know how to use arrays in an optimal way, it can cause you to create slow code. The Dictionary Object takes care of most of the work for you and from what I can tell, are pretty fast. I wish I could get a peek into the code to see how they work.

[david000]

Most of the time I use them for creating a list of unique items

So, if I understand this right by setting the 'index' to 'Nothing' you are sorta using 'Redim Preserve', as in (1 to a Non-duplicate), right?

Also,

What is the deal with Excel using Option Text Compare and vbTextCompare in the same language, or is this just a .Net thing?


Sub RemoveDups()
Dim a, b, c As Variant
a = Array("A", "a", "b", "B", "c", "C") 'Get unique!
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each b In a
If Not IsEmpty(b) Then
If Not .exists(b) Then .Add b, Nothing
End If
Next
c = .Keys
End With
MsgBox Join(c, vbNewLine) 'I dig this use of Join early in the post by Hobs!




I usually use Nothing if I don't need to associate the Keys with a Value or vice versa. :doh: That's confusing!

[malik641]

So, if I understand this right by setting the 'index' to 'Nothing' you are sorta using 'Redim Preserve', as in (1 to a Non-duplicate), right? Hey David,

Basically. But it's not so much that I'm using the index or Nothing, but the Add operation is what is similar to the idea of Redim Preserve. Using the dictionary object is more efficient.

I'm pretty sure Redim Preserve allocates memory AND performs a copy operation (of the addresses of the strings), which can get heavy for a long list (why else would arrays ONLY be allowed to be passed By Reference?). Redim alone I think will just allocate memory because it doesn't "keep" any values.



Also,

What is the deal with Excel using Option Text Compare and vbTextCompare in the same language, or is this just a .Net thing?


Sub RemoveDups()
Dim a, b, c As Variant
a = Array("A", "a", "b", "B", "c", "C") 'Get unique!
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each b In a
If Not IsEmpty(b) Then
If Not .exists(b) Then .Add b, Nothing
End If
Next
c = .Keys
End With
MsgBox Join(c, vbNewLine) 'I dig this use of Join early in the post by Hobs!

Option Compare Text is for an entire module, where using vbTextCompare can be used in a single instance like in the Instr function.



:doh: That's confusing! lol, I thought I was in the clear when I put the capital 'N' in "Nothing". :) sorry!