user defined function works in VBA but does not work inside worksheets

Hi,
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error

The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.

I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.

The code is :

PHP Code: 

Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double
    Dim I As Integer, Slope As Double, NData As Integer
    
    NData = UBound(X)
    For I = 1 To UBound(X) - 1
        If (X(I) = X0) Then
            Interpolate = Y(I)
            Exit Function
        ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then
            Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1))
            Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1))
            Exit Function
        End If
    Next I
        
End Function

Public Function ListMax(ParamArray ListItems() As Variant)

        Dim I As Integer
        ListMax = ListItems(0)
        For I = 0 To UBound(ListItems())
            If ListItems(I) > ListMax Then ListMax = ListItems(I)
        Next I

End Function

Public Function ListMin(ParamArray ListItems() As Variant)

        Dim I As Integer
        ListMin = ListItems(0)
        For I = 0 To UBound(ListItems())
            If ListItems(I) < ListMin Then ListMin = ListItems(I)
        Next I
End Function 


I appreciate your help. Thanks

What does the code look like when you use the function in code?

Can you post some test data with the expected result.

I didnt understand your first question, but abut your latter question (I have copied and pasted the code in my previous post), some test data are as:

Column A....column B

1...............10
2...............20

If my X0 is 1.5, I expect 15 as the result of linear interpolation. The function is able to calculate it correctly (in VAB) but in excel I get error as I said.

In fact the spreadsheet realizes that there is a function named INTERPOLATE, but it can not call it properly (or its better to say that I can not call it properly)

I assume when testing your code in VBA you used something like this.

Sub x()

    Dim dblX(1 To 2) As Double
    Dim dblY(1 To 2) As Double
    
    dblX(1) = 1
    dblX(2) = 2
    dblY(1) = 10
    dblY(2) = 20
    
    MsgBox Interpolate(dblX, dblY, 1.5)
    
End Sub

Which passes arrays declared as double into the routine.

Your problem is your are not passing arrays declared as double when using

=interpolate(A1,A2,B1,B2,1.5)

You need to adapt your routine to handle ranges or variants.

Is your routine different to the TREND function?

=TREND(B1:B2,A1:A2,1.5)