SOLVED: Which loop to use?
Hi there,
I'm trying to work out which loop I need to use or if I need to use a mix of loops.
The context is as follows:
I have a table of absorption values for each material.
I have a button which calculates which class of absorber the material is, based on its 5 values
Classes are A to E, plus unclassified. (A is the best, Unclassified is the worst)
The 5 values are stored in an array and so I want to loop through this array and find the lowest value and return that one.
For example, if out of the 5 values in the array, the first two are a D, the second two are an E and the last one is a D, I would want to stop at the first E and return "E".
I have made my code so far as a For Each loop, but it goes through all 5 values and the result is the last one, regardless and I am stuck. Do I need, for example to use a "Do While" type loop instead? If so, I've never used one of these before and would probably need some asssistance.
My code is as follows (the first bit is the calculation and then the loop comes later):
I look forward to your help. Thank you in advance.
Hi there,
I'm trying to work out which loop I need to use or if I need to use a mix of loops.
The context is as follows:
I have a table of absorption values for each material.
I have a button which calculates which class of absorber the material is, based on its 5 values
Classes are A to E, plus unclassified. (A is the best, Unclassified is the worst)
The 5 values are stored in an array and so I want to loop through this array and find the lowest value and return that one.
For example, if out of the 5 values in the array, the first two are a D, the second two are an E and the last one is a D, I would want to stop at the first E and return "E".
I have made my code so far as a For Each loop, but it goes through all 5 values and the result is the last one, regardless and I am stuck. Do I need, for example to use a "Do While" type loop instead? If so, I've never used one of these before and would probably need some asssistance.
My code is as follows (the first bit is the calculation and then the loop comes later):
Code:
Private Sub btnClassCalculate_Click()
Dim strClass(0 To 4) As String
Dim Str As Variant
Erase strClass
Select Case Me.num250Hz
Case 0.7 To 1
strClass(0) = "A"
Case 0.6 To 0.7
strClass(0) = "B"
Case 0.4 To 0.6
strClass(0) = "C"
Case 0.1 To 0.4
strClass(0) = "D"
Case Is < 0.1
strClass(0) = "E"
End Select
Select Case Me.num500Hz
Case 0.92 To 1
strClass(1) = "A"
Case 0.84 To 0.92
strClass(1) = "B"
Case 0.64 To 0.84
strClass(1) = "C"
Case 0.32 To 0.64
strClass(1) = "D"
Case 0.16 To 0.32
strClass(1) = "E"
Case Is < 0.16
strClass(1) = "U"
End Select
Select Case Me.num1kHz
Case 0.92 To 1
strClass(2) = "A"
Case 0.84 To 0.92
strClass(2) = "B"
Case 0.64 To 0.84
strClass(2) = "C"
Case 0.32 To 0.64
strClass(2) = "D"
Case 0.16 To 0.32
strClass(2) = "E"
Case Is < 0.16
strClass(2) = "U"
End Select
Select Case Me.num2kHz
Case 0.92 To 1
strClass(3) = "A"
Case 0.84 To 0.92
strClass(3) = "B"
Case 0.64 To 0.84
strClass(3) = "C"
Case 0.32 To 0.64
strClass(3) = "D"
Case 0.16 To 0.32
strClass(3) = "E"
Case Is < 0.16
strClass(3) = "U"
End Select
Select Case Me.num4kHz
Case 0.8 To 1
strClass(4) = "A"
Case 0.72 To 0.8
strClass(4) = "B"
Case 0.51 To 0.72
strClass(4) = "C"
Case 0.24 To 0.51
strClass(4) = "D"
Case Is < 0.24
strClass(4) = "E"
End Select
'loop through the array and search for class types.
For Each Str In strClass
Select Case Str
Case Is = "U"
MsgBox ("Unclassified Absorber") 'print message
Me.chrAbsorptionClass = "Unclassified"
Exit For
Case Is = "E"
MsgBox "Class E exists" 'print message
Me.chrAbsorptionClass = "E"
Exit For
Case Is = "D"
MsgBox "Class D exists" 'print message
Me.chrAbsorptionClass = "D"
Exit For
Case Is = "C"
MsgBox "Class C exists" 'print message
Me.chrAbsorptionClass = "C"
Exit For
Case Is = "B"
MsgBox "Class B exists" 'print message
Me.chrAbsorptionClass = "B"
Exit For
Case Else
MsgBox "Class A exists" 'print message
Me.chrAbsorptionClass = "A"
Exit For
End Select
Next
Erase strClass
End Sub
Last edited: