Problem with VBA Functions that use a DefinedRange as parameter

[Harold]

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...

 

[Charles Williams]

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]

Calling the function using N(DefinedName) seems to be the best solution I
have come up with. ie. fxVal(N(TestRange)) or fxTimesTwo(N(TestRange))...

I'm sure there is a hidden index in the passed DefinedName, but I can not
find reference to it. I say this because Microsoft designed functions ie
SQRT(TestRange) work fine.

Any other help appreciated...

 

[Harold]

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]

Harold,

You are right: try this way (in real life you would need error handling
etc.)

Public Function Test3(ByRef var As Variant) As Variant

Dim vv As Variant
Dim jThisRow As Long
Dim jStartRow As Long

jStartRow = var.Row
jThisRow = Application.Caller.Row
vv = var(jThisRow - jStartRow + 1, 1).Value
vv = vv * 2
Test3 = vv

End Function

 

[Harold]

Excellet!!!!
I did not pick up that I could get the address of the passed range. You
have made my day Charles, thanks!

 

[Charles Williams]

This extended version works the same way as Excel functions with array
formulae, but only handles ranges and Named ranges as input parameters.
For true generality it should also handle a wider variety of input
parameters such as scalars, array constants and calculated inputs such as
your N(TestRange)

Public Function Test4(ByRef Var As Variant) As Variant

Dim vv As Variant
Dim va() As Variant
Dim jThisRow As Long
Dim jStartRow As Long
Dim j As Long
Dim nCallerRows As Long

If Not Application.Caller.HasArray Then
'
' Implicit Intersect with Row
'
jThisRow = Application.Caller.Row
jStartRow = Var.Row

vv = Var(jThisRow - jStartRow + 1, 1).Value2
vv = vv * 2
Test4 = vv
Else
'
' array formula: Implicit Intersect with the Array Formula
'
nCallerRows = Application.Caller.Rows.Count
ReDim va(1 To nCallerRows, 1 To 1) As Variant
vv = Var.Value2
For j = 1 To nCallerRows
va(j, 1) = vv(j, 1) * 2
Next j
Test4 = va
End If

End Function


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