VBA Excel Question

Posted on 2007-07-26
Last Modified: 2013-12-25
I have a small script in Excel VBA and I'm using a Dictionary object.

I want to print all they key-value pairs  how do I do this?

Debug.Print "Key=" & ???  & " Value=" & ???

Short example?
Question by:tmonteit
    LVL 58

    Expert Comment

    For Each Key In DictionaryObject
      Debug.Print "Key=" & Key  & " Value=" & DictionaryObject.Item(Key)
    LVL 81

    Accepted Solution

    Here is some general information about using the Dictionary object.

    The scripting dictionary has a couple of significant advantages over collections: it is faster for numbers of elements less than 40 or 50 thousand and it allows for testing whether or not a key exists. The scripting dictionary can store any type of variable or object but it is best suited for storing objects that need to be accessed with keys. Arrays are a much more efficient method for storing simple variables.

    To use the scripting dictionary a reference must be established to the "Microsoft Scripting Runtime" library unless late binding is used (see below). The sample code below illustrates how to define, initialize, load, access, and clear a scripting dictionary using objects.

       Dim Dictionary As Scripting.Dictionary
       Dim Index As Long
       Dim ClassObject As clsClass

       ' Initialize the dictionary
       Set Dictionary = New Scripting.Dictionary

       ' Load the dictionary with objects using an index value for a key
       For Index = 1 To 100
          Set ClassObject = New clsClass
          Dictionary.Add CStr(Index), ClassObject
       Next Index

       ' Read a specific element
       Set ClassObject = Dictionary("23")
       ' Read each object - this requires an intermediate variant because the default type
       ' of a dictionary's item is not compatible with an object of a specific type
       Dim ElementObject As Variant
       For Each ElementObject In Dictionary.Items
          Set ClassObject = ElementObject
       Next ElementObject

       ' Test if a key exists
       If Dictionary.Exists("23") Then
          MsgBox "Key 23 exists"
       End If

       ' Set the compare mode to TextCompare
       Dictionary.CompareMode = TextCompare

       ' Change the key of an element
       Dictionary.Key("23") = "ABC"

       ' Extract items into an array
       Dim DictionaryItems As Variant
       DictionaryItems = Dictionary.Items

       ' Extract keys into an array
       Dim DictionaryKeys As Variant
       DictionaryKeys = Dictionary.Keys

       ' Clear an element from the dictionary

       ' Clear the entire dictionary

       ' Use late binding (no reference to the "Microsoft Scripting Runtime" library is required)
       Dim Dictionary As Object
       Set Dictionary = CreateObject("Scripting.Dictionary")


    Author Comment

       Dim dict As Dictionary
        Set dict = New Dictionary
          With dict
         'set compare mode
         .CompareMode = BinaryCompare
         ' Add items to the dictionary.
         .Add "foo 1", "bar 1"
         .Add "foo 2", "bar 2"
         .Add "foo 3", "bar 3"
       End With

      For Each Item In dict.Items
            Debug.Print Item
            '  ??  now what ??
    LVL 92

    Assisted Solution

    by:Patrick Matthews
       For Each k In dic.Keys
            Debug.Print "Key: " & k & "; Value: " & dic.Item(k)

    Author Comment

    sorry zv, I think you've answered it.  didn't see your post before i hit send.
    LVL 58

    Expert Comment

    Could I ask why you did not consider http:#19577665 or if there was something wrong in it, I would like to know :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now