Alain, thank you very much for your quick reply.

OK so I can make it work nicely for the instance that I'm passing an array
in from another sub-routine, but what about the instance where I use the
function as a worksheet function with excel range syntax as the argument.
This is where I'm having trouble because VBA can not give me the bounds on
this variant. Is there any way to make this function work for both cases?

"Alain Vaillancourt" wrote:

The declaration of xrange must be an array for UBound to work, now it is a
Variant type containing an array.

If xrange is not always containing Single values you can declare it as an
array of Variant like this:
Function interpolate(Value, xrange(), yrange)

If xrange is always containing Single values you are better to declare it as
an array of Single like this:
Function interpolate(Value, xrange() as Single, yrange)



"TomLegado" wrote in message
...
I've got the following function that I would like to make versatile enough
that I can use it within VBA code and as a simple excel worksheet
function.
I've pasted the function here where I'm having trouble, I need to figure
out
how to count the elements in the range for both types of data. The "Size"
variable where I'm getting the dimension works with the UBound if my data
type is an array or variant, but I have to use Xrange.Count if the data is
an
excel workbook range. Neither option works for both instances. Here is
my
code:

Sub TestFunc()
Dim Test1(3) As Single, Test2(3) As Single, Value As Single, Value2 As
Single

Test1(1) = 3
Test1(2) = 2
Test1(3) = 1
Test2(1) = 1
Test2(2) = 2
Test2(3) = 3
Value = 2.5

Value2 = interpolate(Value, Test1, Test2)

Worksheets("Input Page").Select
Range("P25") = Value2


End Sub




Function interpolate(Value, xrange, yrange)
' Function linearly interpolates from a given x/y range
' Function allows for extrapolation outside the known range
'
Dim Size As Integer, Cumiminus1 As Single, Cumi As Single
Dim X1 As Single, X2 As Single, Y1 As Single, Y2 As Single
'
' Size = xrange.Count
' Size = 3
Size = UBound(xrange)
'


Thanks, Tom