A faster way to write for each statement in VBA

Question

I am writing VBA code to populate three different combined fields with the same data. I just wondered if there is a more efficient way to write it then, what am I doing at the moment?

' Create fac1 cbo For Each c_fac In ws_misc.Range("fac") With Me.cbo_fac1 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With Next c_fac ' Create fac2 cbo For Each c_fac In ws_misc.Range("fac") With Me.cbo_fac2 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With Next c_fac ' Create fac3 cbo For Each c_fac In ws_misc.Range("fac") With Me.cbo_fac3 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With Next c_fac 

Thanks for taking the time!

Answer 1

One more step, perhaps:

 dim lLoop as long ' Create fac1 cbo For Each c_fac In ws_misc.Range("fac") For lLoop=1 to 3 Me.controls("cbo_fac" & lLoop).AddItem c_fac.Value Me.controls("cbo_fac" & lLoop).List(Me.controls("cbo_fac" & lLoop).ListCount - 1, 1) = c_fac.Offset(0, 1).Value next lLoop Next c_fac 

Answer 2

Why can't you do this?

 ' Create fac1 cbo For Each c_fac In ws_misc.Range("fac") With Me.cbo_fac1 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With With Me.cbo_fac2 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With With Me.cbo_fac3 .AddItem c_fac.Value .List(.ListCount - 1, 1) = c_fac.Offset(0, 1).Value End With Next c_fac 

This reduces the amount of time you need to iterate over the range of worksheets by 2 / 3rds. Usually reading and writing to the actual objects in an Excel worksheet is what takes the most time in Excel VBA code.

Answer 3

This would be much faster (assuming the "fac" is a column) .AddItem is very slow for> 5 entries:

 Dim rng rng = ws_misc.Range("fac").resize(,2).value Me.cbo_fac1.List = rng Me.cbo_fac2.List = rng Me.cbo_fac3.List = rng 

Answer 4

Personally, I don't like With in this case. It doubles the number of lines of code without any benefits. You can reduce this to the end:

 ' Create fac1 cbo For Each c_fac In ws_misc.Range("fac") Me.cbo_fac1.AddItem c_fac.Value Me.cbo_fac1.List(Me.cbo_fac1.ListCount - 1, 1) = c_fac.Offset(0, 1).Value Me.cbo_fac2.AddItem c_fac.Value Me.cbo_fac2.List(Me.cbo_fac2.ListCount - 1, 1) = c_fac.Offset(0, 1).Value Me.cbo_fac3.AddItem c_fac.Value Me.cbo_fac3.List(Me.cbo_fac3.ListCount - 1, 1) = c_fac.Offset(0, 1).Value Next c_fac