-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Scripting dictionary/Exits/Keys/Items etc...
- Thread starter Siyanna
- Start date
If you Google it there's a lot of information:-
You might find this interesting reading:-
Introducing...the Dictionary
The associative array, often called a Map or Hash Map, is one of the most useful data structures available to the programmer. Unlike a normal indexed array, the contents of an associative array are accessed via a 'key' rather than a numerical index. So, instead of having to keep track of where things are in an array, the programmer can assign a unique key to a value and then add the key, value pair to the array. Retrieving the value is just then a case of using the key. For example key value pairs could be names and ages: John, 34; Jane, 46; Ted, 102 etc.
In languages such as Java and C# there are entire families of associative arrays available via collections frameworks. In other languages such as Python, Ruby or Groovy the map is a basic part of the language. What about VBA? Well, it just so happens that there is a form of associative array called the Dictionary that VBA programmers can use. This tutorial will be using Excel as the application, but the same principles are available in Word, PowerPoint and anything else that uses VBA.
To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code.
With that in place we can write some code to put the Dictionary object through its paces:
Sub DictExample1()
Dim dict As Dictionary
Dim v As Variant
'Create the dictionary
Set dict = New Dictionary
'Add some (key, value) pairs
dict.Add "John", 34
dict.Add "Jane", 42
dict.Add "Ted", 402
'How many items do we have?
Debug.Print "Number of items stored: " & dict.Count
'We can retrieve an item based on the key
Debug.Print "Ted is " & dict.Item("Ted") & " years old"
'We can test whether an item exists
Debug.Print "We have Jane's age: " & dict.Exists("Jane")
Debug.Print "We have Zak's age " & dict.Exists("Zak")
'We can update a value by replacing it
dict.Item("Ted") = dict.Item("Ted") / 10
Debug.Print "Ted's real age is: " & dict.Item("Ted")
'We can add more items
dict.Add "Carla", 23
'And we can iterate through the complete dictionary
For Each v In dict.Keys
Debug.Print "Name: " & v & "Age: "; dict.Item(v)
Next
End Sub
that 's all well and good and straightforward to do, but how do you use one in real life? OK. Here's a common scenario: Every month you get a workbook that contains a sheet of raw data – sales, orders, exam results, telephone calls etc. This table of data contains some unique fields – sales by salesperson, orders by region, exam results by subject etc. You need to extract the data by these fields to different worksheets. One way is to write some VBA code that runs through the raw data and just copies and pastes the data to the different sheets. The only thing is you need to keep track of where the next row is on these different sheets. This is where the dictionary comes in handy – each field can act as a key in a dictionary, and the next blank row is the value that corresponds to that key.
As an example here's a table of raw exam data by subject:
Name Date Subject Score
Alex 04/02/2009 Biology 60.00
Alex 06/03/2009 English 60.00
Angela 04/02/2009 Biology 47.00
Angela 06/03/2009 Physics 47.00
Bharat 02/02/2009 English 64.00
Bharat 04/03/2009 English 64.00
Christine 03/02/2009 Physics 52.00
Christine 05/03/2009 Physics 52.00
George 03/02/2009 Physics 71.00
George 05/03/2009 English 71.00
Gilbert 03/02/2009 Physics 14.00
Gilbert 05/03/2009 Physics 14.00
Jane 02/02/2009 English 56.00
Jane 04/03/2009 Biology 56.00
Peter 02/02/2009 English 45.00
Peter 03/02/2009 Physics 52.00
Peter 04/03/2009 English 45.00
Peter 05/03/2009 Physics 52.00
The VBA code to do the processing looks like this:
Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
subject = Worksheets("Data").Cells(i, 3).Value
score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
targetRow = dict.Item(subject)
Else
targetRow = 1
End If
Worksheets(subject).Cells(targetRow, 1) = name
Worksheets(subject).Cells(targetRow, 2) = score
dict.Item(subject) = targetRow + 1
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False
Wend
End Sub
Running this allows us to process the data and produce a sheet that looks like this:
Alex 60
Bharat 64
Bharat 64
George 71
Jane 56
Peter 45
Peter 45
While this has been a very quick introduction to the Dictionary, don't be fooled by how easy it is to use. The Dictionary is a pretty powerful object, and for complex applications where you need more than one level of indirection, it is possible to have a dictionary that contains other dictionaries as keyed values - which we look at in part 2. All in all it's a powerful addition to your VBA coding set.
===
A Dictionary of Dictionaries
AS we discovered in the first part of this series on the VBA Dictionary Object, the Dicttionary Object gives VBA developers access to an associative array that allows us to store items in an array that is keyed by a value rather than just a numerical index. In other words you supply a unique key, such as a text string representing a name, and are able to store a value against it. Often this value is quite simple, a number, another string and so on. But what if what you want to store is another dictionary? What then?
Well, it turns out that this is straightforward to do using object references to access each of the dictionary objects that are stored in the main dictionary. First let's describe the scenario we want to model using a dictionary of dictionaries …
Imagine that each month we want to process the total sales of different products by salesperson. Each month we have a different range of products and different salespeople. We can't use fix arrays because we don't know in advance the number of different products or the people selling them. The data structure is simple though. Three columns of data: Name, Product, Number, with one row per record, and there can be multiple records for each person/product combination, as shown below:
Name Product Number
Alan Widget 10
Alan Widget 8
Jane Prong 5
AS shown previously, the first thing to do is to create a reference to the Microsoft Scripting Runtime, which is the DLL that contains the Dictionary (as well as the FileSystemObject). To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code.
With that in place we can write some code to process the data using the dictionaries. In this case we'll create a dictionary for each salesperson to include the total sales for each product they have sold. Each of these dictionaries will be stored in one main dictionary that will be keyed by the salespersons name.
Sub processData()
Dim peopleDictionary As Dictionary
Dim salesDictionary As Dictionary
Dim r As Integer
Dim salesPerson As Variant
Dim prod As Variant
Dim amount As Integer
'main dictionary to contain other dictionaries
Set peopleDictionary = New Dictionary
'dictionary for sales - to be used once per person
Set salesDictionary = New Dictionary
r = 2 'first row contains header, so start at row 2
salesPerson = ActiveSheet.Cells(r, 1)
prod = ActiveSheet.Cells(r, 2)
amount = ActiveSheet.Cells(r, 3)
'keep processing data until we run out of salespeople...
While Len(salesPerson) > 0
If peopleDictionary.Exists(salesPerson) Then
'sales person already has a sales dictionary - so lets grab it
Set salesDictionary = peopleDictionary(salesPerson)
If salesDictionary.Exists(prod) Then
'already have a total for this product, so lets update it
amount = amount + salesDictionary(prod)
salesDictionary(prod) = amount
Else
'new product, so add it to dictionary with current amount
salesDictionary.Add prod, amount
End If
Else
'new salesperson, so create dictionary and add product total
Set salesDictionary = New Dictionary
salesDictionary.Add prod, amount
'add salesperson and their dictionary to the main dictionary
peopleDictionary.Add salesPerson, salesDictionary
End If
r = r + 1
salesPerson = ActiveSheet.Cells(r, 1)
prod = ActiveSheet.Cells(r, 2)
amount = ActiveSheet.Cells(r, 3)
Wend
'And we can iterate through the complete dictionary to show what we have
For Each salesPerson In peopleDictionary.Keys
Set salesDictionary = peopleDictionary(salesPerson)
For Each prod In salesDictionary.Keys
Debug.Print "Name: " & salesPerson & " : Product: " & prod & " : Sales: " & salesDictionary(prod)
Next
Next
End Sub
This is all very straightforward apart from where we re-use the saleDictionary variable. At first glance you'd think we would be overwriting this constantly because we use the same variable name to point to different peoples sales figures. However, the trick is in the use of the Set keyword. In object oriented terms Dictionary variables are what are termed object references rather than basic variables like Integer or Float. A Dictionary variable is a label that can be attached and re-attached to different data structures. In our case we simply re-use the variable salesDictionary to point to different instances of Dictionary objects, so we never over-write one person's data with anothers.
And that's all there is to using one dictionary object to contains others. In real life you'd be more likely to handle the example shown here using a Pivot table, but there are plenty of situations where an array of dictionary objects makes a lot of sense to use.
You might find this interesting reading:-
Introducing...the Dictionary
The associative array, often called a Map or Hash Map, is one of the most useful data structures available to the programmer. Unlike a normal indexed array, the contents of an associative array are accessed via a 'key' rather than a numerical index. So, instead of having to keep track of where things are in an array, the programmer can assign a unique key to a value and then add the key, value pair to the array. Retrieving the value is just then a case of using the key. For example key value pairs could be names and ages: John, 34; Jane, 46; Ted, 102 etc.
In languages such as Java and C# there are entire families of associative arrays available via collections frameworks. In other languages such as Python, Ruby or Groovy the map is a basic part of the language. What about VBA? Well, it just so happens that there is a form of associative array called the Dictionary that VBA programmers can use. This tutorial will be using Excel as the application, but the same principles are available in Word, PowerPoint and anything else that uses VBA.
To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code.
With that in place we can write some code to put the Dictionary object through its paces:
Sub DictExample1()
Dim dict As Dictionary
Dim v As Variant
'Create the dictionary
Set dict = New Dictionary
'Add some (key, value) pairs
dict.Add "John", 34
dict.Add "Jane", 42
dict.Add "Ted", 402
'How many items do we have?
Debug.Print "Number of items stored: " & dict.Count
'We can retrieve an item based on the key
Debug.Print "Ted is " & dict.Item("Ted") & " years old"
'We can test whether an item exists
Debug.Print "We have Jane's age: " & dict.Exists("Jane")
Debug.Print "We have Zak's age " & dict.Exists("Zak")
'We can update a value by replacing it
dict.Item("Ted") = dict.Item("Ted") / 10
Debug.Print "Ted's real age is: " & dict.Item("Ted")
'We can add more items
dict.Add "Carla", 23
'And we can iterate through the complete dictionary
For Each v In dict.Keys
Debug.Print "Name: " & v & "Age: "; dict.Item(v)
Next
End Sub
that 's all well and good and straightforward to do, but how do you use one in real life? OK. Here's a common scenario: Every month you get a workbook that contains a sheet of raw data – sales, orders, exam results, telephone calls etc. This table of data contains some unique fields – sales by salesperson, orders by region, exam results by subject etc. You need to extract the data by these fields to different worksheets. One way is to write some VBA code that runs through the raw data and just copies and pastes the data to the different sheets. The only thing is you need to keep track of where the next row is on these different sheets. This is where the dictionary comes in handy – each field can act as a key in a dictionary, and the next blank row is the value that corresponds to that key.
As an example here's a table of raw exam data by subject:
Name Date Subject Score
Alex 04/02/2009 Biology 60.00
Alex 06/03/2009 English 60.00
Angela 04/02/2009 Biology 47.00
Angela 06/03/2009 Physics 47.00
Bharat 02/02/2009 English 64.00
Bharat 04/03/2009 English 64.00
Christine 03/02/2009 Physics 52.00
Christine 05/03/2009 Physics 52.00
George 03/02/2009 Physics 71.00
George 05/03/2009 English 71.00
Gilbert 03/02/2009 Physics 14.00
Gilbert 05/03/2009 Physics 14.00
Jane 02/02/2009 English 56.00
Jane 04/03/2009 Biology 56.00
Peter 02/02/2009 English 45.00
Peter 03/02/2009 Physics 52.00
Peter 04/03/2009 English 45.00
Peter 05/03/2009 Physics 52.00
The VBA code to do the processing looks like this:
Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
subject = Worksheets("Data").Cells(i, 3).Value
score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
targetRow = dict.Item(subject)
Else
targetRow = 1
End If
Worksheets(subject).Cells(targetRow, 1) = name
Worksheets(subject).Cells(targetRow, 2) = score
dict.Item(subject) = targetRow + 1
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False
Wend
End Sub
Running this allows us to process the data and produce a sheet that looks like this:
Alex 60
Bharat 64
Bharat 64
George 71
Jane 56
Peter 45
Peter 45
While this has been a very quick introduction to the Dictionary, don't be fooled by how easy it is to use. The Dictionary is a pretty powerful object, and for complex applications where you need more than one level of indirection, it is possible to have a dictionary that contains other dictionaries as keyed values - which we look at in part 2. All in all it's a powerful addition to your VBA coding set.
===
A Dictionary of Dictionaries
AS we discovered in the first part of this series on the VBA Dictionary Object, the Dicttionary Object gives VBA developers access to an associative array that allows us to store items in an array that is keyed by a value rather than just a numerical index. In other words you supply a unique key, such as a text string representing a name, and are able to store a value against it. Often this value is quite simple, a number, another string and so on. But what if what you want to store is another dictionary? What then?
Well, it turns out that this is straightforward to do using object references to access each of the dictionary objects that are stored in the main dictionary. First let's describe the scenario we want to model using a dictionary of dictionaries …
Imagine that each month we want to process the total sales of different products by salesperson. Each month we have a different range of products and different salespeople. We can't use fix arrays because we don't know in advance the number of different products or the people selling them. The data structure is simple though. Three columns of data: Name, Product, Number, with one row per record, and there can be multiple records for each person/product combination, as shown below:
Name Product Number
Alan Widget 10
Alan Widget 8
Jane Prong 5
AS shown previously, the first thing to do is to create a reference to the Microsoft Scripting Runtime, which is the DLL that contains the Dictionary (as well as the FileSystemObject). To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code.
With that in place we can write some code to process the data using the dictionaries. In this case we'll create a dictionary for each salesperson to include the total sales for each product they have sold. Each of these dictionaries will be stored in one main dictionary that will be keyed by the salespersons name.
Sub processData()
Dim peopleDictionary As Dictionary
Dim salesDictionary As Dictionary
Dim r As Integer
Dim salesPerson As Variant
Dim prod As Variant
Dim amount As Integer
'main dictionary to contain other dictionaries
Set peopleDictionary = New Dictionary
'dictionary for sales - to be used once per person
Set salesDictionary = New Dictionary
r = 2 'first row contains header, so start at row 2
salesPerson = ActiveSheet.Cells(r, 1)
prod = ActiveSheet.Cells(r, 2)
amount = ActiveSheet.Cells(r, 3)
'keep processing data until we run out of salespeople...
While Len(salesPerson) > 0
If peopleDictionary.Exists(salesPerson) Then
'sales person already has a sales dictionary - so lets grab it
Set salesDictionary = peopleDictionary(salesPerson)
If salesDictionary.Exists(prod) Then
'already have a total for this product, so lets update it
amount = amount + salesDictionary(prod)
salesDictionary(prod) = amount
Else
'new product, so add it to dictionary with current amount
salesDictionary.Add prod, amount
End If
Else
'new salesperson, so create dictionary and add product total
Set salesDictionary = New Dictionary
salesDictionary.Add prod, amount
'add salesperson and their dictionary to the main dictionary
peopleDictionary.Add salesPerson, salesDictionary
End If
r = r + 1
salesPerson = ActiveSheet.Cells(r, 1)
prod = ActiveSheet.Cells(r, 2)
amount = ActiveSheet.Cells(r, 3)
Wend
'And we can iterate through the complete dictionary to show what we have
For Each salesPerson In peopleDictionary.Keys
Set salesDictionary = peopleDictionary(salesPerson)
For Each prod In salesDictionary.Keys
Debug.Print "Name: " & salesPerson & " : Product: " & prod & " : Sales: " & salesDictionary(prod)
Next
Next
End Sub
This is all very straightforward apart from where we re-use the saleDictionary variable. At first glance you'd think we would be overwriting this constantly because we use the same variable name to point to different peoples sales figures. However, the trick is in the use of the Set keyword. In object oriented terms Dictionary variables are what are termed object references rather than basic variables like Integer or Float. A Dictionary variable is a label that can be attached and re-attached to different data structures. In our case we simply re-use the variable salesDictionary to point to different instances of Dictionary objects, so we never over-write one person's data with anothers.
And that's all there is to using one dictionary object to contains others. In real life you'd be more likely to handle the example shown here using a Pivot table, but there are plenty of situations where an array of dictionary objects makes a lot of sense to use.
Last edited:
Upvote
0
ZVI
MrExcel MVP
- Joined
- Apr 9, 2008
- Messages
- 3,875
- Office Version
- 2019
- 2016
- 2010
- Platform
- Windows
Hi,
Here are some links to Dictionary object explanations and the usage code examples:
MSDN, The Dictionary Object
MSDN, VBScript, Dictionary Object
Overview of the scripting dictionary object
Using the Dictionary Class in VBA
Regards
Upvote
0
Similar threads
- Question
