Charles,
For some reason my earlier response to you never got posted...

The issue with using Application.Caller.Row or .Address is that it refers to
the location of the calling function and not the index of the range.

In the example, TestRange's first element is in cell A2. The first function
call would be in row 2 which means the call that is suppose to use the first
element is using the second element...

Now obviously I could fix this by using row-1, but then if I inserted a
couple of lines my function would no longer work. Which means I have got to
pass another parameter indicating the index of the element...

Thanks for responding and any other ideas appreciated.

"Charles Williams" wrote:

In column B you are using Implicit Intersection of the Named Range and the
Row.
This does not work for UDFs unless you explicitly code for it.

Public Function Test2(ByRef var As Variant) As Variant

Dim vv As Variant
Dim jThisRow As Long


jThisRow = Application.Caller.Row
vv = var(jThisRow, 1).Value
vv = vv * 2
Test2 = vv

End Function

When you pass a range to a UDF as a variant parameter, the variant parameter
starts off as a range object contained in a variant, but you can get its
values by assigning it to a variant. This conversion from variant containing
a range to Variant containing the values from the range may be done by VBA
under the covers as an implicit conversion if VBA thinks thats required.

If you pass the UDF a calculated parameter INT(TestRange) then its a
multi-stage operation:
first the INT function does the Implicit Intersection, gets the resulting
value and converts it to whole number, then the whole number is converted to
a double, then the double is passed to the UDF as a variant containing a
double.

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Harold" wrote in message
...
If I set up a simple spreadsheet with Column A being a defined range named
TestRange. I can then in column B use the formula =TestRange for each
cell
and get the value that is in A.

But if I create a function
Public Function fxVal(ByVal a As Variant)
fxVal = a
End Function
and copy it down column C I get the value of the first item in the range
only

A B C
TestRange =TestRange =fxVal(TestRange)
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1


Further if I make a column D that is =2*TestRange I get the expected
values,
but if I use a function
Public Function fxTimesTwo(ByVal a As Variant)
fxTimesTwo = 2 * a
End Function

I get #Value! for each cell...

D E
=2*TestRange =fxTimesTwo(TestRange)
2 #VALUE!
4 #VALUE!
6 #VALUE!
8 #VALUE!
10 #VALUE!

In dealing with this issue, I have determined that the DefinedRange is
passed to the function as a VarType=8204 vbArray+vbVariant. I have tried
using CInt to the passed variable and countless other things but cannot
resolve from within the Function.

I can obviously resolve the issue from within the spreadsheet by calling
the
function with cell addresses ie fxVal(A3).
Also, I can use fxVal(Int(TestRange)) and the functions will work. From
researching this it appears that the Int(TestRange) will convert the
parameter to an individual element double...

Obviously, since Excel has countless internal functions, ie
SQRT(TestRange)
that work fine, there should be someway that my man made function should
be
able to handle the named range from within the function.

Any help appreciated...