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.

Please Login or Register  to view this content.

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)

You need to rewrite the function using variants or ranges as the arguments.

UBOUND only works on arrays.

Did you try the TREND function?

I found a way to get the number of chosen cells:
X.cells.count
where X is one of my function arguments.

I think .cells.count is called a Cells Object Property. True?



and after removing UBound from my code , the code is working perfectly.

You could modify your function

Please Login or Register  to view this content.

which will work with the following cell formula

=interpolate({1;2},{10;20},1.5)

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


Q1. The first of the example formula above is using an array, rather than a range.

Q2. UBound will tell you the dimension of an array. A range is not consider an array when used with this function. It is the variable type that is causing the problem not that you are using it from a worksheet cell.

Q3. Cells.Count property of the Range object.