Using Scripting.Dictionary with UDTs

[nals14]

Hi,
I am pretty new to VBA. I have a problem. I posted a question about it and I got a lot of help. I really appreciate all the help.

I have another question now.

I have a class called eg. CBooks.
The members are id,
name,
author,
publisher.

I have a spread sheet which serves as a data entry form for this information.
The user can add new books, delete them from the list and modify them. So at the end of each operation I have to sort the list to maintain the list in order of book name or author name.

I have been doing some research and the dictionary object seems to fit my needs.
Only problem is I do not know if a dictionary can be used with a custom class.:dunno If it can how do you declare and initialze it. I could not find an example for it anywhere.

Could some please help me get started with some examples.:help

Really appreciate your help and time.

[mvidas]

Hi Nals,

I think you'd probably be better off using a collection instead, but you'd know better which to use.

Using a dictionary:Option Explicit
Dim Dict As Dictionary 'set reference to Microsoft Scripting Runtime
'Dim Dict As Object 'to use late-binding (no reference)
Sub nals14exampleDictionary()
Dim aBook As CBooks

Set Dict = New Dictionary ' CreateObject("scripting.dictionary")

Set aBook = New CBooks
aBook.Name = "nals14 example"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook '(Key, Item)

Set aBook = New CBooks
aBook.Name = "nals14 example number 2"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

'until you destroy the Dict variable or RemoveAll, it can now be used anywhere
End Sub


Using a collection:Option Explicit
Dim Coll As Collection
Sub nals14exampleDictionary()
Dim aBook As CBooks

Set Coll = New Collection

Set aBook = New CBooks
aBook.Name = "nals14 example"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Coll.Add aBook '(Item, [Key], [Before], [After])

Set aBook = New CBooks
aBook.Name = "nals14 example number 2"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Coll.Add aBook

'Coll now contains all books entered
End Sub

I made Dict and Coll as public variables, as I wasn't sure the scope of what you're doing. They certainly don't need to be public though if you only need the contents during a single runtime; you can just pass them as arguments to other subs/functions

[nals14]

Hi MVidas,
Thank you so much for your quick response and excellant example.

The reason I was looking at dictionary was that Sorting was supposed to be easy and also I read that you cannot modify a collection Item. eg. In my collection of Books if I want to modify the 5'th book, is it still possible or should I delete book no 5 and add the modified book5 at the end?

[mvidas]

Actually after re-reading, I think a dictionary would be better, since you're gonna want to sort it.

For the Key argument of the dict.add, make that the field you want to sort by. You can later put the Dict.Keys into an array and sort that, then use something like:For i = 0 to Dict.Count - 1
'Dict(SortedKeyArray(i)) now refers to the variable of your class
Next 'i

My apologies, I didn't read enough into the question

[mvidas]

To give you a full example (including sort):Sub nals14()
Dim aBook As CBooks, Dict As Object, TempArr() As Variant, i As Long

Set Dict = CreateObject("scripting.dictionary")

'adding them to the dictionary in non-sorted Name order

Set aBook = New CBooks
aBook.Name = "nals14 example number 3"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

Set aBook = New CBooks
aBook.Name = "nals14 example number 1"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

Set aBook = New CBooks
aBook.Name = "nals14 example number 2"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

TempArr = Dict.Keys

BubbleSort TempArr

For i = LBound(TempArr) To UBound(TempArr)
With Dict(TempArr(i))
MsgBox .Name & vbCrLf & .Author & vbCrLf & .Publisher
End With
Next
End Sub

Sub BubbleSort(MyArray() As Variant)
'modified from http://vbaexpress.com/kb/getarticle.php?kb_id=103
Dim Last As Long, i As Long, j As Long, Temp As Variant
Last = UBound(MyArray)
For i = LBound(MyArray) To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
End Sub

[nals14]

Hi Matt,
Thank you so much for your response. Yes dictionary it is. I am getting started now and will come back here with more questions. Starting off with the adding, modifying and deleting part before getting into sorting.

I understood "For the Key argument of the dict.add, make that the field you want to sort by". But didn't quite follow what you meant by "You can later put the Dict.Keys into an array and sort that"?

I read that for sorting a dictionary you use the following statement.

SortKeys(Dict.Keys,fDescend,vbCompareText)

This will return a variant? Do I have to use the return value? Won't the Dictionary now be sorted and I can iterate over each one them and they will be in sorted order?

I really appreciate your help as I couldn't find information about UDTs with dictionaries all over the internet.

Regards,
Nalini.

[nals14]

Sorry saw your respose just now. Ignore my last post!

Thanks you so much.

[mvidas]

I do see your last post here, just figured I'd post anyways.
The "SortKeys" you're talking about may either be a custom function, or one that my version (admittedly an older one) does not have. If there is already a built-in function for it then you don't need Jake's bubble sort function

[nals14]

Hi Matt,
Thanks for your reply. I thought Dictionary had a built in sorting function:think: . I will use the sorting algorithm you have given.

Thanks once again.:bow:


I do see your last post here, just figured I'd post anyways.
The "SortKeys" you're talking about may either be a custom function, or one that my version (admittedly an older one) does not have. If there is already a built-in function for it then you don't need Jake's bubble sort function

[nals14]

Hi,
I have now run into a different kind of problem. In my case I will have to sort by book name / author name. I have the book id as the key. Sorting by Key is working fine. My questions are :

1. Is it possible to sort on something other than the key. In my case Item is a class with author and book names.
2. Are keys unique in a dictionary

Thanks.

[mvidas]

Hi,
I have now run into a different kind of problem. In my case I will have to sort by book name / author name. I have the book id as the key. Sorting by Key is working fine. My questions are :

1. Is it possible to sort on something other than the key. In my case Item is a class with author and book names.
2. Are keys unique in a dictionary

Thanks.
Hi,
Yes, keys are unique in a dictionary, each dictionary item has it's own key. Having duplicate keys would make item references impossible.

You can pull the Dict.Items in the same way you get Dict.Keys, but the array taken from .Items will be an array of your objects. Unless you have a default property of each item to sort by (more complicated than it is worth here) you won't easily be able to sort the keys.

You should know that you're not actually sorting the dictionary itself. Sorting the .Keys and then iterating through the sorted list is how you're able to get the "sorted" dictionary.

What about using a different key than the book id? Maybe combine two fields, like aBook.Author & "|" & aBook.Name or something to that effect..

[mvidas]

But, to give you an example of how you can sort by something other than the actual key:Sub nals14()
Dim aBook As CBooks, Dict As Object, TempArr() As Variant, i As Long
Dim KeyArr() As Variant, ItemArr() As Variant

Set Dict = CreateObject("scripting.dictionary")

'adding them to the dictionary in non-sorted Name order

Set aBook = New CBooks
aBook.Name = "nals14 example number 3"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

Set aBook = New CBooks
aBook.Name = "nals14 example number 1"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

Set aBook = New CBooks
aBook.Name = "nals14 example number 2"
aBook.Author = "mvidas"
aBook.Publisher = "vbaexpress.com"
Dict.Add aBook.Name, aBook

'***** ADDED THIS PART
KeyArr = Dict.Keys
ItemArr = Dict.Items
ReDim TempArr(LBound(KeyArr) To UBound(KeyArr))
'create new TempArr containing what you want it sorted by, putting
' the key after a null character for easier extraction later
For i = LBound(KeyArr) To UBound(KeyArr)
TempArr(i) = ItemArr(i).Author & "|" & ItemArr(i).Name & Chr(0) & KeyArr(i)
Next 'i

BubbleSort TempArr 'this sorts the array by whatever you put into temparr

For i = LBound(TempArr) To UBound(TempArr)
'extract the key after chr(0)
With Dict(Mid(TempArr(i), InStr(1, TempArr(i), Chr(0)) + 1))
MsgBox .Name & vbCrLf & .Author & vbCrLf & .Publisher
End With
Next
End Sub

Sub BubbleSort(MyArray() As Variant)
'modified from http://vbaexpress.com/kb/getarticle.php?kb_id=103
Dim Last As Long, i As Long, j As Long, Temp As Variant
Last = UBound(MyArray)
For i = LBound(MyArray) To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
End Sub

[nals14]

Hi Matt,
Thank you very much for the idea. I will use the method suggested by you. Thanks a bunch. Will let you know how this goes.

Nalini

[nals14]

:help Hi,
I started to use the dictionary and I have run into a problem. I had given CBooks as a generic example. Let me get to the structure that I am using right now.

CAction
It has :
Reasontext
ActionText
Status
PriorityNo.

I maintain a whole list of this cAction which I add to modify delete and sort. Sorting is done based on the Priority Number. But that is not unique. Everytime I create a new CAction object I assign it a key. This key is going to be appended to the Priority number in the following format :

<priority number>_ <Key>

The priority number is a number between 1 to 35.

If I make the key a number like 1,2,3 etc. U run into the following problem :-
3_5 ( 3 is priority number and 5 is key )
1_7 ( 1 is priority number and 7 is key )
10_8 ( 10 is priority number and 8 is key )

sorts as

10_8
1_7
3_5

So 10 comes first instead of 1 as it is not treated as a number. How do I overcome this problem.

Is it wrong to have chosen a dictionary for this purpose. Should I go back to using arrays or collections?:dunno

Thanks,
Nalini

[mvidas]

Hi Nalini,

A dictionary still seems like it would be best for you; have you thought about formatting the priority number to contain padded zeroes? If you won't have a priority above 99, then one padded zero would be fine, so instead of 3_5, 1_7, and 10_8, you would have 03_5, 01_7, and 10_8. That way the sort would still work fine: dict.Add Format(YourCActionVariable.Priority, "00") & "_" & YourCActionVariable.Key, _
YourCActionVariable

[nals14]

Hi,
Thank you so much for your reply. My priority number will not go above 99. I had started using a another work around but your method is much more efficient. I will use it. Thank you very much once again.

Nalini

Shouldn't we have an article about this kind of Dictionary usage in knowledge base. If it wasn't for you I would have been really struggling as there was not much information to be found anywhere.

I found an article in the internet about Dictionaries and how they differ from collections. It was very useful and I thought I will paste the link here here so you could include that in the knowlegebase?

http://elementkstaff.com/Instructor_Sharing/dlindeman/bm0799.pdf

[mvidas]

An article wouldn't be a bad idea, though there may already be one. I don't really check the articles very often (though after just checking, I don't see one). I know there are a couple KB entries that use dictionaries, nothing explaining how they work though.

By just googling for 'vba dictionary' I saw a few resources that look to contain some decent information, I actually just used a couple help files for it when I first was playing around with them, VBSCRIP5.CHM and VBLR6.CHM (directories may be different so just search if you're interested). I'd write an article but I'm not the greatest writer or anything and don't have a huge amount of freetime lately.
Since you've learned a lot about them and are still learning, why not write one yourself? You've got all the information fresh in your head and I always learn more while trying to teach others than any other time so it will probably help you as well.
Otherwise you could probably put up a request post separately and see if someone will write one, it could be quite useful

[nals14]

Maybe I can come up with a basic document, but I don't think I have enough knowledge to write a knowledge base article on this subject:think:


An article wouldn't be a bad idea, though there may already be one. I don't really check the articles very often (though after just checking, I don't see one). I know there are a couple KB entries that use dictionaries, nothing explaining how they work though.

By just googling for 'vba dictionary' I saw a few resources that look to contain some decent information, I actually just used a couple help files for it when I first was playing around with them, VBSCRIP5.CHM and VBLR6.CHM (directories may be different so just search if you're interested). I'd write an article but I'm not the greatest writer or anything and don't have a huge amount of freetime lately.
Since you've learned a lot about them and are still learning, why not write one yourself? You've got all the information fresh in your head and I always learn more while trying to teach others than any other time so it will probably help you as well.
Otherwise you could probably put up a request post separately and see if someone will write one, it could be quite useful