Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I loop through key/value pairs in a VBA collection?

Tags:

In VB.NET, I can iterate through a dictionary's key/value pairs:

Dictionary<string, string> collection = new Dictionary<string, string>(); collection.Add("key1", "value1"); collection.Add("key2", "value2");  foreach (string key in collection.Keys) {     MessageBox.Show("Key: " + key + ".  Value: " + collection[key]); } 

I know in VBA I can iterate through the values of a Collection object:

Dim Col As Collection Set Col = New Collection Dim i As Integer Col.Add "value1", "key1" Col.Add "value2", "key2"  For i = 1 To Col.Count     MsgBox (Col.Item(i)) Next I 

I also know that I do this with a Scripting.Dictionary VBA object, but I was wondering if this is possible with collections.

Can I iterate through key/value pairs in a VBA collection?

like image 320
Peter Rankin Avatar asked Jan 29 '14 13:01

Peter Rankin


People also ask

Which can loop through objects directly in VBA?

In VBA, you can loop through a set of collections using the 'For Each' loop. Here are some examples of collections in Excel VBA: A collection of all the open Workbooks. A collection of all worksheets in a workbook.

Does VBA do next loop?

Using FOR NEXT Loop in Excel VBA. 'For Next' Loop works by running the loop the specified number of times. For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on..

How do collections work in VBA?

Collections are used to store objects. They are far more flexible than the VBA arrays, whereas arrays have fixed size limits, but readers don't have any fixed size limit at any given point in time and even don't require manual resizing. VBA Collection is very similar to the “VBA Dictionary.


1 Answers

you cannot retrieve the name of the key from a collection. Instead, you'd need to use a Dictionary Object:

Sub LoopKeys()     Dim key As Variant      'Early binding: add reference to MS Scripting Runtime     Dim dic As Scripting.Dictionary     Set dic = New Scripting.Dictionary      'Use this for late binding instead:     'Dim dic As Object     'Set dic = CreateObject("Scripting.Dictionary")      dic.Add "Key1", "Value1"     dic.Add "Key2", "Value2"      For Each key In dic.Keys         Debug.Print "Key: " & key & " Value: " & dic(key)     Next End Sub 
like image 134
Peter Albert Avatar answered Sep 20 '22 15:09

Peter Albert