I've written a VBA UDF to calculate an AQL Sample Size by the Inspection Level (IL Variable in my code), and Lot size (Batch Variable in my code).
In an Access userform, I have the values for my two variables displayed in a text box. I have a third text box which has my UDF, that I want to auto-update to the new value when I add or change either of the two variables. It never changes from zero.
When I update either variable, if I put a stop in the UDF VBA code, the code does run, and provides the correct answer in a Watch in my VBE as I step through.
Also, somewhat related question, I'm guessing you cannot use a UDF in a table? I can use the same UDF in an Excel table, so I was thinking I should be able to.
Public Function SampleSize(IL As String, Batch As Long) As Long
If IL = "S1" Then
Select Case Batch
Case 2 To 50
Sample = 2
Case 51 To 500
Sample = 3
Case 501 To 35000
Sample = 5
Case Is >= 35001
Sample = 8
End Select
End If
If IL = "S2" Then
Select Case Batch
Case 2 To 25
Sample = 2
Case 26 To 150
Sample = 3
Case 151 To 1200
Sample = 5
Case 1201 To 35000
Sample = 8
Case Is >= 35001
Sample = 13
End Select
End If
If IL = "S3" Then
Select Case Batch
Case 2 To 15
Sample = 2
Case 16 To 50
Sample = 3
Case 51 To 150
Sample = 5
Case 151 To 500
Sample = 8
Case 501 To 3200
Sample = 13
Case 3201 To 35000
Sample = 20
Case 35001 To 500000
Sample = 32
Case Is >= 500001
Sample = 50
End Select
End If
If IL = "S4" Then
Select Case Batch
Case 2 To 15
Sample = 2
Case 16 To 25
Sample = 3
Case 26 To 90
Sample = 5
Case 91 To 150
Sample = 8
Case 151 To 500
Sample = 13
Case 501 To 1200
Sample = 20
Case 1201 To 10000
Sample = 32
Case 10001 To 35000
Sample = 50
Case 35001 To 500000
Sample = 80
Case Is >= 500001
Sample = 125
End Select
End If
If IL = "G1" Then
Select Case Batch
Case 2 To 15
Sample = 2
Case 16 To 25
Sample = 3
Case 26 To 90
Sample = 5
Case 91 To 150
Sample = 8
Case 151 To 280
Sample = 13
Case 281 To 500
Sample = 20
Case 501 To 1200
Sample = 32
Case 1201 To 3200
Sample = 50
Case 3201 To 10000
Sample = 80
Case 10001 To 35000
Sample = 125
Case 35001 To 150000
Sample = 200
Case 150001 To 500000
Sample = 315
Case Is >= 500001
Sample = 500
End Select
End If
If IL = "G2" Then
Select Case Batch
Case 2 To 8
Sample = 2
Case 9 To 15
Sample = 3
Case 16 To 25
Sample = 5
Case 26 To 50
Sample = 8
Case 51 To 90
Sample = 13
Case 91 To 150
Sample = 20
Case 151 To 280
Sample = 32
Case 281 To 500
Sample = 50
Case 501 To 1200
Sample = 80
Case 1201 To 3200
Sample = 125
Case 3201 To 10000
Sample = 200
Case 10001 To 35000
Sample = 315
Case 35001 To 150000
Sample = 500
Case 150001 To 500000
Sample = 800
Case Is >= 500001
Sample = 1250
End Select
End If
If IL = "G3" Then
Select Case Batch
Case 2 To 8
Sample = 3
Case 9 To 15
Sample = 5
Case 16 To 25
Sample = 8
Case 26 To 50
Sample = 13
Case 51 To 90
Sample = 20
Case 91 To 150
Sample = 32
Case 151 To 280
Sample = 50
Case 281 To 500
Sample = 80
Case 501 To 1200
Sample = 125
Case 1201 To 3200
Sample = 200
Case 3201 To 10000
Sample = 315
Case 10001 To 35000
Sample = 500
Case 35001 To 150000
Sample = 800
Case 150001 To 500000
Sample = 1250
Case Is >= 500001
Sample = 2000
End Select
End If
End Function
You never assign a return value to the function itself!
You should place this line of code as indicated:
SampleSize = Sample
End Function
Also it is good practice to declare variables within the scope of your function.
Dim Sample As Long
When declared it will be initialized to 0, which will also be your default return value and the same Type as your Function.
Undeclared variables will be Variant type and initialized to Empty...