This is an excerpt of an answer I gave on an Excel VBA mailing list. The original poster asked if I could explain my solution.

Let's look at a single solution not involving a sub routine call. We'll use the Combo Box control named CboMonth. Here's the submitted method with some white space added for us poor human readers. :)

PLAIN TEXT
Visual Basic:
  1. With CboMonth
  2.  
  3.     .AddItem "1"
  4.     .AddItem "2"
  5.     .AddItem "3"
  6.     .AddItem "4"
  7.     .AddItem "5"
  8.     .AddItem "6"
  9.     .AddItem "7"
  10.     .AddItem "8"
  11.     .AddItem "9"
  12.     .AddItem "10"
  13.     .AddItem "11"
  14.     .AddItem "12"
  15.  
  16. End With

The glaring question for an experienced programmer is, "Do numbers have to be quoted in VBA (or in this particular method)?" Since the author asked for advice on simplifying the code we can safely assume he realizes that there is probably a shorter method available to add these items. The answer to the question above is, "No. VBA and AddItem do not require numbers to be forced into strings."

PLAIN TEXT
Visual Basic:
  1. With CboMonth
  2.  
  3.     .AddItem 1
  4.     .AddItem 2
  5.     .AddItem 3
  6.     .AddItem 4
  7.     .AddItem 5
  8.     .AddItem 6
  9.     .AddItem 7
  10.     .AddItem 8
  11.     .AddItem 9
  12.     .AddItem 10
  13.     .AddItem 11
  14.     .AddItem 12
  15.  
  16. End With

This is important because it allows us to replace the integers 1 through 12 with a variable whose value represents an integer without having to first convert that value to a string. While I would never actually write the following code in practice it does illustrate a working solution.

PLAIN TEXT
Visual Basic:
  1. Private Sub UserForm_Initialize()
  2.  
  3.     Dim iInteger
  4.     With CboMonth
  5.  
  6.         iInteger = 1
  7.         .AddItem iInteger
  8.  
  9.         iInteger = iInteger + 1
  10.         .AddItem iInteger
  11.  
  12.         iInteger = iInteger + 1
  13.         .AddItem iInteger
  14.  
  15.         iInteger = iInteger + 1
  16.         .AddItem iInteger
  17.  
  18.         iInteger = iInteger + 1
  19.         .AddItem iInteger
  20.  
  21.         iInteger = iInteger + 1
  22.         .AddItem iInteger
  23.  
  24.         iInteger = iInteger + 1
  25.         .AddItem iInteger
  26.  
  27.         iInteger = iInteger + 1
  28.         .AddItem iInteger
  29.  
  30.         iInteger = iInteger + 1
  31.         .AddItem iInteger
  32.  
  33.         iInteger = iInteger + 1
  34.         .AddItem iInteger
  35.  
  36.         iInteger = iInteger + 1
  37.         .AddItem iInteger
  38.  
  39.         iInteger = iInteger + 1
  40.         .AddItem iInteger
  41.  
  42.     End With
  43.  
  44. End Sub

Again, not better code in any sense, but it does illustrate what the solution I provided does in a long winded fashion. In VBA, there is a control statement which will loop through a series of integers or a list of some kind and allow operations on each value before going to the next value in the list.

We know ahead of time that we need to add items to this control from a sequence of integers numbered 1 to 12. VBA gives us a For ... Next loop for this purpose. This code is equivalent to the code above. On each pass through the loop, iInteger increments by the value in the "Step" clause.

PLAIN TEXT
Visual Basic:
  1. Private Sub UserForm_Initialize()
  2.  
  3.     Dim iInteger
  4.  
  5.     With CboMonth
  6.  
  7.         For iInteger = 1 To 12 Step 1
  8.             .AddItem iInteger
  9.         Next iInteger
  10.  
  11.     End With
  12.  
  13. End Sub

We don't really need the "With" statement and we can leave the Step clause off if we are incrementing by +1.

PLAIN TEXT
Visual Basic:
  1. Private Sub UserForm_Initialize()
  2.  
  3.     Dim iInteger
  4.     For iInteger = 1 To 12
  5.         CboMonth.AddItem iInteger
  6.     Next iInteger
  7.  
  8. End Sub

To make this into a more general case, we need to first allow any object which supports the AddItem method to be used.

PLAIN TEXT
Visual Basic:
  1. Private Sub UserForm_Initialize()
  2.  
  3.     AddMonth CboMonth
  4.  
  5. End Sub
  6.  
  7. Private Sub AddMonth(object)
  8.  
  9.     Dim iInteger
  10.     For iInteger = 1 To 12
  11.         object.AddItem iInteger
  12.     Next iInteger
  13.  
  14. End Sub

Since there are cases where we may want to add other sequential ranges of integers, we can make this even more general. We replace the 1 above with the lower value of the range and the 12 above with the upper value of the supplied range.

PLAIN TEXT
Visual Basic:
  1. Private Sub UserForm_Initialize()
  2.  
  3.     ' Initialize Combo Boxes
  4.     AddIntegerItems CboDay, 1, 31
  5.     AddIntegerItems CboMonth, 1, 12
  6.     AddIntegerItems CboYear, 2006, 2011
  7.  
  8. End Sub
  9.  
  10.  
  11. ' Assumes an object which supports AddItem method
  12. Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)
  13.  
  14.     Dim iInteger
  15.     For iInteger = iRangeLow To iRangeHigh
  16.         object.AddItem iInteger
  17.     Next iInteger
  18.  
  19. End Sub

Another way to look at this is to replace the general variable with specific ones. For example, "AddIntegerItems CboDay, 1, 31" is the same as this. We replace iRangeLow with 1; iRangeHigh becomes 31; and object becomes CboDay.

PLAIN TEXT
Visual Basic:
  1. Dim iInteger
  2.     For iInteger = 1 To 31
  3.         CboDay.AddItem iInteger
  4.     Next iInteger

Look familiar?