First, we'll place the indentation back in. We do not write
computer programs only for machines. We also write them for human
beings and human beings like white space, comments, appropriate
punctuation and consistency. These visual cues make reading code
easier and makes code more maintainable.
Squashingeverythingtogethermakesitharderoneveryoneexceptthemachine.
Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)
Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger
End Sub
Second, I am not at your beck and call. I do not fetch or roll
over. I will not get your slippers. You need to make an effort to
understand what is happening here and you can do the best reading
the documentation and by experimenting, not by demanding that I
supply an explanation for every line in my solution. If you have
more specific questions, ask them.
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. :)
With CboMonth
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
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."
With CboMonth
.AddItem 1
.AddItem 2
.AddItem 3
.AddItem 4
.AddItem 5
.AddItem 6
.AddItem 7
.AddItem 8
.AddItem 9
.AddItem 10
.AddItem 11
.AddItem 12
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.
Private Sub UserForm_Initialize()
Dim iInteger
With CboMonth
iInteger = 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
iInteger = iInteger + 1
.AddItem iInteger
End With
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.
Private Sub UserForm_Initialize()
Dim iInteger
With CboMonth
For iInteger = 1 To 12 Step 1
.AddItem iInteger
Next iInteger
End With
End Sub
We don't really need the "With" statement and we can leave the
Step clause off if we are incrementing by +1.
Private Sub UserForm_Initialize()
Dim iInteger
For iInteger = 1 To 12
CboMonth.AddItem iInteger
Next iInteger
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.
Private Sub UserForm_Initialize()
AddMonth CboMonth
End Sub
Private Sub AddMonth(object)
Dim iInteger
For iInteger = 1 To 12
object.AddItem iInteger
Next iInteger
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.
Private Sub UserForm_Initialize()
' Initialize Combo Boxes
AddIntegerItems CboDay, 1, 31
AddIntegerItems CboMonth, 1, 12
AddIntegerItems CboYear, 2006, 2011
End Sub
' Assumes an object which supports AddItem method
Private Sub AddIntegerItems(object, iRangeLow, iRangeHigh)
Dim iInteger
For iInteger = iRangeLow To iRangeHigh
object.AddItem iInteger
Next iInteger
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.
Dim iInteger
For iInteger = 1 To 31
CboDay.AddItem iInteger
Next iInteger
Look familiar?