VBA Selecting Array Values Between Specified Values.

VBARich

New Member
Joined
Apr 3, 2019
Messages
3
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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

VBARich

New Member
Joined
Apr 3, 2019
Messages
3
<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

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
How about using a function to get the drill sizes?

Something like this:
Code:
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.
Code:
cmbDrillSizes.List = GetDrillSizes(11.41, 12.3)
 

VBARich

New Member
Joined
Apr 3, 2019
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,282
Members
418,185
Latest member
snoogz2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top