Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11324
  • Last Modified:

VBA Excel Question

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?
2 Solutions
For Each Key In DictionaryObject
  Debug.Print "Key=" & Key  & " Value=" & DictionaryObject.Item(Key)
zorvek (Kevin Jones)ConsultantCommented:
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")

tmonteitAuthor Commented:
   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 ??
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Patrick MatthewsCommented:
   For Each k In dic.Keys
        Debug.Print "Key: " & k & "; Value: " & dic.Item(k)
tmonteitAuthor Commented:
sorry zv, I think you've answered it.  didn't see your post before i hit send.
Could I ask why you did not consider http:#19577665 or if there was something wrong in it, I would like to know :)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now