Search code examples
excelvbarangeuser-defined-functions

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


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: Data_Table_1

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

This the sheet with actual error: Value error

Any help is appreciated.


Solution

  • 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.