Problem setting cell formula to addin function

Question

I'm trying to have a interpolation function that I created in a cell formula but it returns an error 1004 "error defined by the application or the object". The code is hard to read with the weird syntax so be ready. I'll show just code snippets since i don't think the entire code is relevant.

Here i do a loop to find 4 ranges and store the addresses in stat_array (the values look like this :'Courbe SYMOS'!$Y$3:$Y$116) :

For i = 1 To 4
    Set s = ws.Rows(1).Find(what:="s" & i, lookat:=xlWhole, LookIn:=xlValues)
    
    stat_array(i, 1) = "'" & ws.Name & "'" & "!" & ws.Range(s.Offset(2, -1), s.Offset(2, -1).End(xlDown)).Address
    stat_array(i, 2) = "'" & ws.Name & "'" & "!" & ws.Range(s.Offset(2, 0), s.End(xlDown)).Address
Next i

here i create the string variable with the function :

rng2.Offset(2 + k, 0).Formula = "=Interp(" & stat_array(i, 1) & ";" & stat_array(i, 2) & ";" & Pstat.Address & "+" & A & ")"

The result looks like this :

=(Interp('Courbe SYMOS'!$Y$3:$Y$116;'Courbe SYMOS'!$Z$3:$Z$116;$A$3+B3)

Here's the function i'm using :

Public Function Interp(ByVal X_range As Range, ByVal Y_range As Range, ByVal X_val As Double) As Variant
    
    Dim i As Long
    Dim X As Variant, Y As Variant
    X = X_range.Value
    Y = Y_range.Value

    If X_val < X(1, 1) Or X_val > X(X_range.Cells.Count, 1) Then
        Interp = "X2 est en dehors des bornes de X1"
        Exit Function
    End If

    For i = 1 To X_range.Cells.Count - 1
        If X_val >= X(i, 1) And X_val <= X(i + 1, 1) Then
            Interp = Y(i, 1) + (X_val - X(i, 1)) * (Y(i + 1, 1) - Y(i, 1)) / (X(i + 1, 1) - X(i, 1))
            Exit Function
        End If
    Next i
    
    Interp = 0
    
End Function

If i try to take the output and place it the cell the function doesnt update and I have to either close and open the workbook or reset the Application.Calculation to xlCalculationAutomatic manually. Both the function and the module are in the same xlam addin but they're not in the workbook, if anyone knows what's going on i'd love some help.