CODEWITHSUNDEEP
excelvbacombobox
Auborey
Auborey

Reputation: 91

VBA iteratively set variables

I am trying to find the best way to have my combo box selections (inside my user form) be "aliases" for the actual number they represent. In example, if the users selects "A" in the combo box, this will signify 4.0. I am trying to find a way to set my variables iteratively, so I don't have to repeat the same select case code 9 times (for my 9 combo boxes. I am trying to do something like this: I would like the GradeBox (GradeBox_0, GradeBox_1, and so on)

For i = 0 To GRADE_BOX_COUNT

    Select Case GradeBox_0.ListIndex

    Case 0
        Grades(i) = 4
    Case 1
        Grades(i) = 3.7
    Case 2
        Grades(i) = 3.3
    Case 3
        Grades(i) = 3
    Case 4
        Grades(i) = 2.7
    Case 5
        Grades(i) = 2.3
    Case 6
        Grades(i) = 2
    Case 7
        Grades(i) = 1.7
    Case 8
        Grades(i) = 1.3
    Case 9
        Grades(i) = 1
    Case 10
        Grades(i) = 0.7
    Case 11
        Grades(i) = 0
    End Select

Next i

Upvotes: 1

Views: 44

Answers (1)

user3598756
user3598756

Reputation: 29421

edited to add a possible shorter grades selecting (see bottom part)

try this code in your UserForm code pane

Option Explicit

Dim GradeBox() As MSForms.ComboBox
Dim Grades() As Double
Dim nGradeBox As Long

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control

    With Me
        ReDim GradeBox(0 To Me.Controls.Count - 1) As MSForms.ComboBox
        ReDim Grades(0 To Me.Controls.Count - 1) As Double
        For Each ctrl In .Controls
            If TypeName(ctrl) = "ComboBox" And Left(ctrl.Name, 8) = "GradeBox" Then
                Set GradeBox(nGradeBox) = ctrl
                ctrl.RowSource = ActiveSheet.Range("A1:A11").Offset(, nGradeBox).Address '<== here I filled comboboxes with ranges vakues
                nGradeBox = nGradeBox + 1
            End If
        Next ctrl
    End With
    ReDim Preserve GradeBox(0 To nGradeBox - 1) As MSForms.ComboBox
    ReDim Preserve Grades(0 To nGradeBox - 1) As Double
End Sub

Private Sub CommandButton1_Click()
    Dim i As Long
    With Me
        For i = 0 To nGradeBox - 1
            Select Case GradeBox(i).ListIndex
                Case 0
                    Grades(i) = 4
                Case 1
                    Grades(i) = 3.7
                Case 2
                    Grades(i) = 3.3
                Case 3
                    Grades(i) = 3
                Case 4
                    Grades(i) = 2.7
                Case 5
                    Grades(i) = 2.3
                Case 6
                    Grades(i) = 2
                Case 7
                    Grades(i) = 1.7
                Case 8
                    Grades(i) = 1.3
                Case 9
                    Grades(i) = 1
                Case 10
                    Grades(i) = 0.7
                Case 11
                    Grades(i) = 0
            End Select
        Next i
    End With
End Sub

Where I assumed all relevant comboboxes have names beginning with "GradeBox"

As you see, there's no need to preset the number of "GradeBox" comboboxes, since it's detected at runtime via the UserForm_Initialize sub.

you may also want to consider the following grades switching codes

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim gradesArr As Variant

    gradesArr = Array(4, 3.7, 3.3, 3, 2.7, 2.3, 2, 1.7, 1.3, 1, 0.7, 0) '<== list here the grades. they'll be associated with their index in the array
    For i = 0 To nGradeBox - 1
        If GradeBox(i).ListIndex > -1 And GradeBox(i).ListIndex <= UBound(gradesArr) Then Grades(i) = gradesArr(GradeBox(i).ListIndex)
    Next i
End Sub

another possible and shorter way is

Private Sub CommandButton1_Click()
    Dim i As Long
    For i = 0 To nGradeBox - 1
        Grades(i) = Choose(GradeBox(i).ListIndex + 1, 4, 3.7, 3.3, 3, 2.7, 2.3, 2, 1.7, 1.3, 1, 0.7, 0)'<== list here the grades. they'll be associated with their position form 1 to n)
    Next i
End Sub

but this latter has no control over the index being within actual list range

Upvotes: 1

Related Questions