循环组合框 VBA

我陷入了无法找到正确语法的困境。 我正在处理的代码如下所示：

Public Function Initializecolors()
'initialize the colors by setting them with the help of the webpage:
'http://dmcritchie.mvps.org/excel/colors.htm
'example. Black is the first color
'0(since black is the first index selection) + 1 = 1
Color(0) = 1    'Black
Color(1) = 3    'Red
Color(2) = 4    'Green
Color(3) = 5    'Blue
Color(4) = 7    'Magenta
Color(5) = 9    'Brown

'Study each combobox of colors and if some color
'is selected, initialize it directly to the Indexvariable
Select Case UserForm2.ComboBox7.ListIndex
    Case 0
        colorComboBoxIndex(0) = Color(0)
    Case 1
        colorComboBoxIndex(0) = Color(1)
    Case 2
        colorComboBoxIndex(0) = Color(2)
    Case 3
        colorComboBoxIndex(0) = Color(3)
    Case 4
        colorComboBoxIndex(0) = Color(4)
    Case 5
        colorComboBoxIndex(0) = Color(5)
End Select

Select Case UserForm2.ComboBox8.ListIndex
    Case 0
        colorComboBoxIndex(1) = Color(0)
    Case 1
        colorComboBoxIndex(1) = Color(1)
    Case 2
        colorComboBoxIndex(1) = Color(2)
    Case 3
        colorComboBoxIndex(1) = Color(3)
    Case 4
        colorComboBoxIndex(1) = Color(4)
    Case 5
        colorComboBoxIndex(1) = Color(5)
End Select

Select Case UserForm2.ComboBox9.ListIndex
    Case 0
        colorComboBoxIndex(2) = Color(0)
    Case 1
        colorComboBoxIndex(2) = Color(1)
    Case 2
        colorComboBoxIndex(2) = Color(2)
    Case 3
        colorComboBoxIndex(2) = Color(3)
    Case 4
        colorComboBoxIndex(2) = Color(4)
    Case 5
        colorComboBoxIndex(2) = Color(5)
End Select

   Select Case UserForm2.ComboBox10.ListIndex
    Case 0
        colorComboBoxIndex(3) = Color(0)
    Case 1
        colorComboBoxIndex(3) = Color(1)
    Case 2
        colorComboBoxIndex(3) = Color(2)
    Case 3
        colorComboBoxIndex(3) = Color(3)
    Case 4
        colorComboBoxIndex(3) = Color(4)
    Case 5
        colorComboBoxIndex(3) = Color(5)
End Select

Select Case UserForm2.ComboBox11.ListIndex
    Case 0
        colorComboBoxIndex(4) = Color(0)
    Case 1
        colorComboBoxIndex(4) = Color(1)
    Case 2
        colorComboBoxIndex(4) = Color(2)
    Case 3
        colorComboBoxIndex(4) = Color(3)
    Case 4
        colorComboBoxIndex(4) = Color(4)
    Case 5
        colorComboBoxIndex(4) = Color(5)
End Select

Select Case UserForm2.ComboBox12.ListIndex
    Case 0
        colorComboBoxIndex(5) = Color(0)
    Case 1
        colorComboBoxIndex(5) = Color(1)
    Case 2
        colorComboBoxIndex(5) = Color(2)
    Case 3
        colorComboBoxIndex(5) = Color(3)
    Case 4
        colorComboBoxIndex(5) = Color(4)
    Case 5
        colorComboBoxIndex(5) = Color(5)
End Select
End Function

然后将这些值应用于下面设置颜色值的代码

 ...For j = 7 to 12
        If colorComboBoxIndex(j) > -1 Then
        ...      
        .Border.ColorIndex = colorComboBoxIndex(j)
        ....

它的功能是这样的。我不需要调试帮助。只是如何使用 for 循环创建第一个代码块的语法。艺术方面的东西：

for j = 0 to 5  
 Select Case UserForm2.ComboBox(j).ListIndex  

我知道这个语法不存在并且尝试过我的运气我的控制但有编译错误。

任何形式的帮助将不胜感激。
亲切的问候，
马里奥

p.s Color() 和 coloComboBox() 变量在其他地方声明为公共

您正在寻找的语法是

Me.Controls.Item("ComboBox" & j)

但是让用户控件保留随机的默认名称是不好的风格。从一开始就为它们指定适当的名称，这样在 VBA 代码中对它们的引用实际上是有意义的。

这是一种更精致的方法：在包含颜色组合框的用户窗体中，编辑它们的属性并命名它们ColorBox_0通过ColorBox_4。然后，在该用户窗体的代码中添加以下内容：

Option Explicit

Private Const COLOR_BOX_COUNT As Integer = 4  ' actually that's 5, as we count from 0

Private Sub UserForm_Initialize()
    Dim cmb As ComboBox, i As Integer

    ' Prepare color combo boxes with actual RGB color codes and names
    For i = 0 To COLOR_BOX_COUNT
        Set cmb = Me.Controls.Item("ColorBox_" & i)
        cmb.Clear
        cmb.ColumnCount = 2
        cmb.ColumnHeads = False
        cmb.ColumnWidths = "0;"
        cmb.AddItem "000000": cmb.Column(1, 0) = "Black"
        cmb.AddItem "FF0000": cmb.Column(1, 1) = "Red"
        cmb.AddItem "00FF00": cmb.Column(1, 2) = "Green"
        cmb.AddItem "0000FF": cmb.Column(1, 3) = "Blue"
        cmb.AddItem "FF00FF": cmb.Column(1, 4) = "Magenta"
        cmb.AddItem "7C2927": cmb.Column(1, 5) = "Brown"
        cmb.MatchRequired = True
        cmb.Value = cmb.List(0)  ' pre-select first entry
    Next i
End Sub

Public Function GetSelectedColors() As Long()
    Dim cmb As ComboBox, i As Integer
    Dim result(COLOR_BOX_COUNT) As Long

    For i = 0 To COLOR_BOX_COUNT
        Set cmb = Me.Controls.Item("ColorBox_" & i)
        If IsNull(cmb.Value) Then
          result(i) = -1
        Else
          result(i) = GetColor(cmb.Value)
        End If
    Next i

    GetSelectedColors = result
End Function

注意如何GetSelectedColors()返回颜色数组。

还有一个辅助函数可以将 RGB 颜色代码转换为数字（颜色为LongVBA 中的值，所以如果你想实际use以某种方式设置颜色，比如设置BackColor的控件，您实际上可以立即使用该值）：

Function GetColor(rgb As Variant) As Long
    If Len(rgb) = 6 And IsNumeric("&H" & rgb) Then
        GetColor = CLng("&H" & Right(rgb, 2) & Mid(rgb, 3, 2) & Left(rgb, 2))
    End If
End Function

有了这一切，你就不需要魔法常量了（1 = Black, 3 = Red）不再，用户窗体在启动时自行引导，全局变量也消失了，这是一件好事。

我所做的唯一约定是颜色值为-1表示用户尚未选择组合框中的项目。这种情况不应该发生，因为组合框从预先选择的第一个条目开始。

现在您可以直接获取选定的颜色

Private Sub TestButton_Click()
    Dim colors() As Long

    colors = Me.GetSelectedColors

    ' do something with them'
End Sub

Or maybe

Private Sub ColorBox_1_Change()
    ColorLabel_1.BackColor = GetColor(ColorBox_1.Value)
End Sub