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

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

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

Sorry I think I answered to quickly, if you need to pass an array or Excel
range you keep it in a Variant and use the IsArray and TypeOf to handle the
difference between them. The next message from Alan in this thread give you
a good example of how to do it...


"TomLegado" wrote in message
...
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

Two clarifying points.
First, it is not necessary to keep the array in a Variant variable; the
following works fine:

Sub abtest3()
Dim arr() As Single, rng As Range
Set rng = Range("C1:C14")
ReDim arr(1 To rng.Count, 1 To 1)
For i = 1 To rng.Count
arr(i, 1) = rng(i)
Next
Size = getSize(arr)
Debug.Print Size, TypeName(arr) '<----Returns 14 Single()
End Sub
Function getSize(xyz)
If IsArray(xyz) Then
If TypeOf xyz Is Range Then
getSize = xyz.Count
Else
getSize = UBound(xyz)
End If
Else
Msg = "Input must be an array or multi-celled range"
MsgBox Msg, 16
End If
End Function

Second, Alain's first response in this thread included the statement
"The declaration of xrange must be an array for UBound to work, now it
is a Variant type containing an array." In fact, UBound works fine on
either an array contained within a Variant variable or a Variant() type
array (or for that matter, any other type of VBA array, e.g., Integer(),
Single(), etc.).

Sub abtest4()
Dim xrange1, xrange2()
xrange1 = Range("C1:C14")
xrange2 = Range("C1:C14")
'xrange1 is an array contained within a Variant type variable;
'xrange2 is a Variant() type array
Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14
End Sub

Alan Beban

Alain Vaillancourt wrote:
Sorry I think I answered to quickly, if you need to pass an array or
Excel range you keep it in a Variant and use the IsArray and TypeOf to
handle the difference between them. The next message from Alan in this
thread give you a good example of how to do it...

To clarify points:

In that example xyz is a Variant, by default when you don't specify a type
it is of type Variant

Second, UBound only work for arrays so if you don't use type checking like
TypeOf or IsArray you will have a problem using it on an Excel Range...


"Alan Beban" wrote in message
...
Two clarifying points.
First, it is not necessary to keep the array in a Variant variable; the
following works fine:

Sub abtest3()
Dim arr() As Single, rng As Range
Set rng = Range("C1:C14")
ReDim arr(1 To rng.Count, 1 To 1)
For i = 1 To rng.Count
arr(i, 1) = rng(i)
Next
Size = getSize(arr)
Debug.Print Size, TypeName(arr) '<----Returns 14 Single()
End Sub
Function getSize(xyz)
If IsArray(xyz) Then
If TypeOf xyz Is Range Then
getSize = xyz.Count
Else
getSize = UBound(xyz)
End If
Else
Msg = "Input must be an array or multi-celled range"
MsgBox Msg, 16
End If
End Function

Second, Alain's first response in this thread included the statement "The
declaration of xrange must be an array for UBound to work, now it is a
Variant type containing an array." In fact, UBound works fine on either
an array contained within a Variant variable or a Variant() type array (or
for that matter, any other type of VBA array, e.g., Integer(), Single(),
etc.).

Sub abtest4()
Dim xrange1, xrange2()
xrange1 = Range("C1:C14")
xrange2 = Range("C1:C14")
'xrange1 is an array contained within a Variant type variable;
'xrange2 is a Variant() type array
Debug.Print UBound(xrange1), UBound(xrange2) '<----Returns 14 14
End Sub

Alan Beban

Alain Vaillancourt wrote:
Sorry I think I answered to quickly, if you need to pass an array or
Excel range you keep it in a Variant and use the IsArray and TypeOf to
handle the difference between them. The next message from Alan in this
thread give you a good example of how to do it...

Assuming that your arrays are 1-based, 1-dimensional (or single column)
arrays, you might consider the folowing snippet:

If IsArray(xrange) Then
If TypeOf xrange Is Range Then
Size = xrange.Count
Else
Size = UBound(xrange)
End If
Else
Msg = "Input must be an array or multi-celled range"
MsgBox Msg, 16
End If

Alan Beban

TomLegado wrote:
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

Alan, thanks that works very well.

"Alan Beban" wrote:

Assuming that your arrays are 1-based, 1-dimensional (or single column)
arrays, you might consider the folowing snippet:

If IsArray(xrange) Then
If TypeOf xrange Is Range Then
Size = xrange.Count
Else
Size = UBound(xrange)
End If
Else
Msg = "Input must be an array or multi-celled range"
MsgBox Msg, 16
End If

Alan Beban

TomLegado wrote:
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