I am getting a Value error when calling a VBA function in Excel

Question

I have two VBA Excel functions: IdentifyOutliers and OutlierString. OutlierString calls IdentifyOutliers. IdentifyOutliers finds outliers in a named Excel range (Data_Table_1) in the same workbook. OutlierString then returns a sentence naming the outliers, such as "The outliers are 45, 65." When I place =OutlierString(Data_Table_1) in a cell and calculate sheet I get value error.

I know IdentifyOutliers works because use alone it returns outliers but spills them into adjacent cells which is undesirable. When I use them together I get #Value error. The sheets are set on manual calculate.

These are my functions:

Function IdentifyOutliers(rng As Range) As Variant
Dim mean As Double
Dim Q1 As Double
Dim Q2 As Double
Dim Q3 As Double
Dim IQR As Double
Dim n As Long
Dim i As Long
Dim cell As Range
Dim z As Double
Dim outliers() As Variant
Dim outlierCount As Long

n = rng.count
Q1 = Application.WorksheetFunction.Quartile_Inc(rng, 1)
Q2 = Application.WorksheetFunction.Quartile_Inc(rng, 2)
Q3 = Application.WorksheetFunction.Quartile_Inc(rng, 3)
IQR = Q3 - Q1

outlierCount = 0
ReDim outliers(1 To n)

For Each cell In rng
    
    If cell.Value > Q3 + 1.5 * IQR Or cell.Value < Q1 - 1.5 * IQR Then
        outlierCount = outlierCount + 1
        outliers(outlierCount) = cell.Value
    End If
Next cell

If outlierCount = 0 Then
    IdentifyOutliers = "No outliers found."
Else
    ReDim Preserve outliers(1 To outlierCount)
    IdentifyOutliers = outliers
End If
End Function

and the 2nd function:

Function OutlierString(dataRange As Range) As String
Dim outliers() As Variant
Dim i As Long
Dim result As String
Dim count As Long


outliers = IdentifyOutliers(dataRange) 

count = UBound(outliers) - LBound(outliers) + 1
If count > 0 Then
    result = "The outliers are "
    For i = LBound(outliers) To UBound(outliers)
        result = result & outliers(i)
        If i < UBound(outliers) Then
            result = result & ", "
        End If
    Next i
Else
    result = "No outliers found."
End If

OutlierString = result
End Function

This is Data_Table_1:

The named range is only A2:D7. Does not include label in 1st row.

This the sheet with actual error:

Any help is appreciated.

To debug, it's better to call this from a Sub. You'll be able to identify the problematic line(s). — BigBen
– BigBen, Commented Sep 9, 2024 at 17:11
have you tried setting a breakpoint at the start of the function and stepping through it to see where things go south? — sous2817
– sous2817, Commented Sep 9, 2024 at 17:12

rotabor · Accepted Answer · 2024-09-09 17:31:51Z

3

The correct code for OutlierString is below:

Function OutlierString(dataRange As Range) As String
  Dim outliers As Variant ' the first correction
  Dim i As Long
  Dim result As String
  Dim count As Long
  outliers = IdentifyOutliers(dataRange)
  If IsArray(outliers) Then ' the second correction
    count = UBound(outliers) - LBound(outliers) + 1
    If count > 0 Then
      result = "The outliers are "
      For i = LBound(outliers) To UBound(outliers)
        result = result & outliers(i)
        If i < UBound(outliers) Then
          result = result & ", "
        End If
      Next i
    Else
      result = "No outliers found."
    End If
  Else
    result = outliers
  End If
  OutlierString = result
End Function

Key points are commented in the code.

answered Sep 9, 2024 at 17:31

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2024-09-10 20:28:04Z

Outliers

The technicalities of 'your demise' are explained in rotabor's answer. I only offer some ideas and improvements.

Highlights

You could increase efficiency by introducing an array to hold the range values in the 1st (main) function.
You could simplify the 2nd function by introducing the VBA.Join function.

Main

This one does the 'heavy lifting'.

Function GetOutlierRow(ByVal rng As Range) As Variant
    
    Const cSCALE As Double = 1.5
    Const cMINIMUM As Long = 4 ' adjust: some say 20, some 30!?
    
    Dim CellsCount As Long: CellsCount = rng.Cells.Count
    If CellsCount <= cMINIMUM Then Exit Function
    
    Dim RowsCount As Long: RowsCount = rng.Rows.Count
    Dim ColumnsCount As Long: ColumnsCount = rng.Columns.Count
    
    Dim Data() As Variant: Data = rng.Value
    
    Dim Q1 As Double: Q1 = Application.WorksheetFunction.Quartile_Inc(rng, 1)
    Dim Q3 As Double: Q3 = Application.WorksheetFunction.Quartile_Inc(rng, 3)
    
    Dim IQR As Double: IQR = Q3 - Q1
    Dim QMin As Double: QMin = Q1 - cSCALE * IQR
    Dim QMax As Double: QMax = Q3 + cSCALE * IQR
    
    Dim Outliers() As Variant: ReDim Outliers(1 To CellsCount)
    
    Dim r As Long, c As Long, OutlierCount As Long
    
    For r = 1 To RowsCount
        For c = 1 To ColumnsCount
            Select Case Data(r, c)
                Case Is < QMin, Is > QMax
                    OutlierCount = OutlierCount + 1
                    Outliers(OutlierCount) = Data(r, c)
            End Select
        Next c
    Next r
    
    If OutlierCount = 0 Then Exit Function
        
    ReDim Preserve Outliers(1 To OutlierCount)
    
    GetOutlierRow = Outliers

End Function

The Calling Procedures

Function GetOutlierInfo( _
    ByVal rng As Range, _
    Optional ByVal Delimiter As String = ", ") _
As String
         
    Dim Outliers As Variant: Outliers = GetOutlierRow(rng)
    
    Dim Result As String
    
    If IsEmpty(Outliers) Then ' a Variant is empty by default
        Result = "No outliers found."
    Else
        Result = "The outliers are " & Join(Outliers, ", ") ' i.e. 'VBA.Join'
    End If
    
    GetOutlierInfo = Result

End Function

Function GetOutlierCount(ByVal rng As Range) As Long
    On Error Resume Next
        GetOutlierCount = UBound(GetOutlierRow(rng))
    On Error GoTo 0
End Function

Function GetOutlierColumn(ByVal rng As Range) As Variant
    On Error Resume Next
        GetOutlierColumn = Application.Transpose(GetOutlierRow(rng))
    On Error GoTo 0
End Function

Collectives™ on Stack Overflow

I am getting a Value error when calling a VBA function in Excel

2 Answers 2

Comments

Outliers

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Outliers

Comments

Your Answer

Sign up or log in

Post as a guest

Related