excel - 在 VBA 中更快速地编写每个语句

Question

我正在编写 VBA 代码来用相同的数据填充三个不同的组合框。我只是想知道是否有比我现在正在做的更有效的编写方式？

' 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

感谢您抽出时间!

Answer 1

更进一步，可能:

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