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?
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.
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..
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With