I was trying to convert the following VBA Sub into a user-defined function that calculates a statistic. The Sub works without any issues. I couldn’t figure out why I got “#Value” error when the Sub was converted into a user-defined function. Here’s the code for the Subroutine:
Sub MyQuartile()
Dim Quartile As Double
Dim nRows As Integer
Dim Rng As Range
Dim QA As Double
Dim QB As Double
Dim CEIL As Double
Dim QAV As Double
Dim QBV As Double
Dim Parameter As Integer
Set Rng = Selection
Parameter = 2 ' Choice of Parameter: 1, 2, 3
nRows = WorksheetFunction.Count(Rng)
QA = ((nRows / 4) * Parameter)
QB = QA + 1
CEIL = WorksheetFunction.Ceiling(QA, 1)
QAV = WorksheetFunction.Small(Rng, QA)
QBV = WorksheetFunction.Small(Rng, QB)
If QA = Int(QA) Then
Quartile = (QAV + QBV) / 2
Else
Quartile = WorksheetFunction.Small(Rng, CEIL)
End If
' Message Box Output
If Parameter = 1 Then
MsgBox "The " & Parameter & "st quartile is: " & Quartile
Else
If Parameter = 2 Then
MsgBox "The " & Parameter & "nd quartile is: " & Quartile
Else
MsgBox "The " & Parameter & "rd quartile is: " & Quartile
End If
End If
End Sub
The following is the code for the UDF:
Function MyQuartile(DataRange As Range, Parameter As Integer) As Double
Dim Quartile As Double
Dim nRows As Integer
Dim Rng As Range
Dim QA As Double
Dim QB As Double
Dim CEIL As Double
Dim QAV As Double
Dim QBV As Double
Rng = DataRange
nRows = WorksheetFunction.Count(Rng) ' Determine number of observations in the set
QA = ((nRows / 4) * Parameter)
QB = QA + 1
CEIL = WorksheetFunction.Ceiling(QA, 1)
QAV = WorksheetFunction.Small(Rng, QA)
QBV = WorksheetFunction.Small(Rng, QB)
' Calculate Quartile
If QA = Int(QA) Then
Quartile = (QAV + QBV) / 2
Else
Quartile = WorksheetFunction.Small(Rng, CEIL)
End If
End Function
