Iterate over VBA Dictionaries?

Question

I'm using the Dictionary class in the MS Runtime Scripting library to store where labels are going to go for a report template. Is there a way to iterate over all the key value pairs in that dictionary like in Python? I just want to use the key as the row number (It's all going in column A) and the value will be the label header.

Something like:

For Each key in dict
    Range("A" & key).Value = dict(key)
Next key

Answer 1

Try:

For Each strKey In oDic.Keys()
    Range("A" & strKey).Value = oDic(strKey)
Next

Answer 2

I'm not sure I understand your question, but maybe this will be of use in an Access context.

Each report and form has a Controls collection. You can walk through it like this:

  Dim ctl As Control

  For Each ctl In Me.Controls
    If ctl.ControlType = acLable
       Debug.Print ctl.Name & ": " & ctl.Caption
    End If
  Next ctl
  Set ctl = Nothing

Now, assume you have a series of textboxes named like:

  txtAmt01
  txtAmt02
  txtAmt03
  txtAmt04
  txtAmt05
  txtAmt06
  txtAmt07
  txtAmt08
  txtAmt09
  txtAmt10

Then assume that each of those controls has a label attached (the names don't matter).

Now, supposed you want to load a pair of values into those controls from a recordset sorted by descending amount that looks like this:

   Person   Amt
   Art     $105
   Jill     $78
   Jack     $50
   Mary     $50
   Jim      $25
   Bill     $18
   Bob      $10
   Bart      $5
   Sue       $5
   Will      $3

Say that you wanted to poke their names and amounts into the captions and textboxes numbered 1-10 listed above. What you'd do is open your recordset:

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim i As Integer

  Set db = CurrentDB
  Set rs = db.OpenRecordset("SELECT Person, Amt FROM MyTable ORDER BY Amt, Person DESC")
  If rs.Recordcount > 0 Then
     rs.MoveFirst
     Do Until rs.EOF
        i = i + 1
        Me("txtAmt" & Format(i,"00")) = rs!Amt 
        Me("txtAmt" & Format(i,"00")).Controls(0).Caption = rs!Person
        rs.MoveNext
     Loop
  End If
  rs.Close
  Set rs = Nothing
  Set db = Nothing

The looping and the counter are pretty self-evident for constructing the numbered names of the controls. The tricky part is the knowing how to stick a value in the caption of the control's label without needing to know the name of the label, and that's done by using the first item in the textbox's Controls collection (there can never be any more than one item in that collection, actually, unless it's a control like an option group, which can have multiple children).

Perhaps this is not what you needed, but maybe it will help someone else.

And, of course, there's also using custom collections, but that's really not useful in forms so much as in reports, where you might want to change the characteristics of a group of controls as you arrive on each record. But I'll leave that as an exercise to the reader...