excel - Looping through members of a collection, how can I get the member's key? -

so have move bunch of spinner objects right next set of cells.

for each spinner need run these statements

worksheets("serial").spnaspect.left = worksheets("serial").range("i12").left - worksheets("serial").spnaspect.width worksheets("serial").spnaspect.top = worksheets("serial").range("i12").top + worksheets("serial").range("i12").height / 2 _                                         - worksheets("serial").spnaspect.height / 2 

so wanted make bit more tidy made following sub. issue i'm having here how tell cell line each spinner with. "i12" in each iteration should thiscontrol 's key in collection.

sub movespinners()      dim mycontrols new collection     dim thiscontrol object     dim mysheet worksheet      mycontrols.add worksheets("serial").spnheight, "i11"     mycontrols.add worksheets("serial").spnaspect, "i12"     mycontrols.add worksheets("serial").spncropleft, "i13"     mycontrols.add worksheets("serial").spncropright, "i14"     mycontrols.add worksheets("serial").spncroptop, "i15"     mycontrols.add worksheets("serial").spncropbottom, "i16"      set mysheet = worksheets("serial")      each thiscontrol in mycontrols          thiscontrol.left = mysheet.range("i12").left - thiscontrol.width         thiscontrol.top = mysheet.range("i12").top + thiscontrol.height / 2 _                                         - thiscontrol.height / 2     next  end sub 

this same question get key of item on collection object , in case answered "use dictionary object instead" in case doesn't work less tidy copy-pasting same lines whole bunch of times

other alternative considering fmovespinner (thisspinner object, mydestination range) hoping keep small movespinner subroutine self-contained.

if have better idea glad hear !

i refactored code use scripting dictionary. looks pretty tidy me!

the keys , items of scripting dictionary can both objects. here stored control key , range item. in way, when access key control item range reference.

before , after

code

sub movespinners2()      dim mycontrols object     dim thiscontrol object     dim mysheet worksheet     dim x long      set mycontrols = createobject("scripting.dictionary")      worksheets("serial")          mycontrols.add .spnheight, .range("i11")         mycontrols.add .spnaspect, .range("i12")         mycontrols.add .spncropleft, .range("i13")         mycontrols.add .spncropright, .range("i14")         mycontrols.add .spncroptop, .range("i15")         mycontrols.add .spncropbottom, .range("i16")      end      each thiscontrol in mycontrols          thiscontrol.left = mycontrols(thiscontrol).left - thiscontrol.width         thiscontrol.top = mycontrols(thiscontrol).top + thiscontrol.height / 2 _                           - thiscontrol.height / 2     next  end sub