VBA Selecting Array Values Between Specified Values.

[VBARich]

Hi,

I am stuck on the following problem:

I have created a VBA Excel Userform that calculates the max and min hole size for a given bolt size.

All calculations etc are contained within the code on the form, I have not and do not want to reference a spreadsheet.

I’ve added a Combobox to list all suitable standard drill sizes that fall between the maximum and minimum hole sizes but I want it to show only the ones suitable as to not have a hole too large or too small.

Example:

For M12 Bolt with 0.25mm Tolerance

MaxHoleSizeBox = 12.3
MinHoleSizeBox = 11.41

From 10mm and up the drill sizes increase in steps of 0.5mm, therefore:

Suitable Drill Sizes = 11.5mm and 12mm

Note that drill sizes up to 10mm increase in steps of 0.1mm while drill sizes above 10mm increase in steps of 0.5mm as shown in the Array below.

What I have so far:

Private Sub MinHoleSize_Change()

On Error Resume Next

'Array List of Standard Drill Sizes

arrList = Array(5, 5.1, 5.2, 5.3, 5.4, 5.5, 5.6, 5.7, 5.8, 5.9, 6, 6.1, 6.2, 6.3, 6.4, 6.5, 6.6, 6.7, 6.8, 6.9, 7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.6, 7.7, 7.8, 7.9, 8, 8.1, 8.2, 8.3, 8.4, 8.5, 8.6, 8.7, 8.8, 8.9, 9, 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 9.7, 9.8, 9.9, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5, 14, 14.5, 15, 15.5, 16, 16.5, 17, 17.5, 18, 18.5, 19, 19.5, 20, 20.5, 21, 21.5, 22, 22.5, 23, 23.5, 24, 24.5, 25, 25.5, 26, 26.5, 27, 27.5, 28, 28.5, 29, 29.5, 30, 30.5, 31, 31.5, 32, 32.5, 33, 33.5, 34, 34.5, 35, 35.5, 36, 36.5, 37, 37.5, 38)

DrillBox.List = arrList

End Sub

How can I populate the ComboBox with only the standard drill sizes from the Array that fall between the max and min specified hole size?

 

[VBARich]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Dim i As Variant

For Each i In arrList
If i>=MinHoleSizeBox And i<=MaxHoleSizeBox Then
YourCombobox.AddItem (i)
End If
Next

End Sub</code></pre>

 

[Norie]

How about using a function to get the drill sizes?

Something like this:

Function GetDrillSizes(MinHoleSize As Double, MaxHoleSize As Double) As Variant
Dim arrDrills()
Dim sz As Double
Dim cnt As Long

    For sz = 5 To 10 Step 0.1
    
        If sz > MinHoleSize And sz < MaxHoleSize Then
            cnt = cnt + 1
            ReDim Preserve arrDrills(1 To cnt)
            
            arrDrills(cnt) = sz
        End If
        
    Next sz
    
    For sz = 10 To 38 Step 0.5
        If sz > MinHoleSize And sz < MaxHoleSize Then
            cnt = cnt + 1
            ReDim Preserve arrDrills(1 To cnt)
            
            arrDrills(cnt) = sz
        End If
        
    Next sz

    GetDrillSizes = arrDrills
        
End Function

Which you could use like this.

cmbDrillSizes.List = GetDrillSizes(11.41, 12.3)

 

[VBARich]

Thanks, I went with this code in the end as it utilized less code:

'List of standard drill sizes
arrList = Array(5, 5.1, 5.2, 5.3, 5.4, 5.5, 5.6, 5.7, 5.8, 5.9, 6, 6.1, 6.2, 6.3, 6.4, 6.5, 6.6, 6.7, 6.8, 6.9, 7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.6, 7.7, 7.8, 7.9, 8, 8.1, 8.2, 8.3, 8.4, 8.5, 8.6, 8.7, 8.8, 8.9, 9, 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 9.7, 9.8, 9.9, 10, 10.5, 11, 11.5, 12, 12.5, 13, 13.5, 14, 14.5, 15, 15.5, 16, 16.5, 17, 17.5, 18, 18.5, 19, 19.5, 20, 20.5, 21, 21.5, 22, 22.5, 23, 23.5, 24, 24.5, 25, 25.5, 26, 26.5, 27, 27.5, 28, 28.5, 29, 29.5, 30, 30.5, 31, 31.5, 32, 32.5, 33, 33.5, 34, 34.5, 35, 35.5, 36, 36.5, 37, 37.5, 38)


Dim i As Variant
Dim MinDia As Double
Dim MaxDia As Double


MinDia = MinDiaBox.Value
MaxDia = MaxDiaBox.Value


For Each i In arrList
If i >= MinDia And i <= MaxDia Then
DrillBox.AddItem (i)
End If
Next i

End Sub