Because VBA returns #value error

Question

Hello I have the following vba code which is a function that takes three parameters and calculates the functions Bult-in trend and rquad and depending on the result it returns trend or the average of the last values.

Function CAPACIDADEPORT(KY As Range, KX As Range, UX)

    Dim RQUAD As Double
    Dim TENDE As Variant
    
    RQUAD = Application.WorksheetFunction.RSq(KY.Value, KX.Value)
    TENDE = Application.WorksheetFunction.Trend(KY.Value, KX.Value, UX.Value)
    
    If RQUAD > 0.6 Then
        CAPACIDADEPORT = TENDE
        
    Else:
        CAPACIDADEPORT = Application.WorksheetFunction.Average(KY.Cells(KY.Cells.Count).Offset(-4).Resize(5))
    End If
        
End Function

This function above works normally. The problem is when I add one more test in if(TENDE > 0), after I change it always returns value error being that the data I pass as parameter for both are the same.

Function CAPACIDADEPORT(KY As Range, KX As Range, UX)
    
        Dim RQUAD As Double
        Dim TENDE As Variant
        
        RQUAD = Application.WorksheetFunction.RSq(KY.Value, KX.Value)
        TENDE = Application.WorksheetFunction.Trend(KY.Value, KX.Value, UX.Value)
        
        If RQUAD > 0.6 AND TENDE > 0 Then
            CAPACIDADEPORT = TENDE
            
        Else:
            CAPACIDADEPORT = Application.WorksheetFunction.Average(KY.Cells(KY.Cells.Count).Offset(-4).Resize(5))
        End If
            
    End Function

I wonder what could be leading to that mistake. Grateful from now on.

Answer 1

To documentation of the function leaves to be desired by informing that it returns a Variant when in fact it returns a Array de Variant. You can check this by using the Vartype.

In my tests, even when I passed UX with only one cell he would return one Array with 2 values, the second value being the trend itself (I’m not sure what came in the first element). Adding (1) in front of the calculation of TENDE corrected the behavior.

Follow the corrected code:

Function CAPACIDADEPORT(KY As Range, KX As Range, UX As Range)
    
    Dim RQUAD As Double
    Dim TENDE As Variant
    
    RQUAD = Application.WorksheetFunction.RSq(KY, KX)
    TENDE = Application.WorksheetFunction.Trend(KY, KX, UX)(1)
    
    If RQUAD > 0.6 And TENDE > 0 Then
        CAPACIDADEPORT = TENDE
        
    Else:
        CAPACIDADEPORT = Application.WorksheetFunction.Average(KY.Cells(KY.Cells.Count).Offset(-4).Resize(5))
    End If

End Function