本文共 5809 字，大约阅读时间需要 19 分钟。

转自

在VBA中使用Dictionary容器，需要做一番设置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.

 

 

Introducing...the DictionaryThe 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.

 

 

 

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 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 IntegerDim targetRow As IntegerDim name As StringDim subject As StringDim score As DoubleDim more As Boolean

Set dict = New Dictionary

more = True

i = 2

Worksheets("English").UsedRange.Clear

Worksheets("Physics").UsedRange.ClearWorksheets("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. All in all it's a powerful addition to your VBA coding set.

 

转载地址：http://iaqbi.baihongyu.com/