I'm trying to use .SpillParent and .SpillingToRange on Range objects in Excel in some UDF's and get unexpected behaviors when the range is not on the same worksheet that the UDF is called from.
(I know that I don't need a UDF like this if I use the "#" spill operator; I'm just using a very simple example to illustrate the problem).
Function MyUDF(ByRef Target As Range) As Range
Set MyUDF = Target.SpillParent
End Function
If [Sheet2!$C$2:$C$10] is a range with spilling results from the formula in $C$2, and in [Sheet1!$A$1], I have "=MyUDF(Sheet2!C5)", I get the value returned from [Sheet1!$C$2], not [Sheet2!$C$2].
Example:
With [Sheet2!$C$2:$C$10] = {1,2,3,4,5,6,7,8,9},
and [Sheet1!$C$2] = 57,
Having "=MyUDF(Sheet2!C5)" in any cell on Sheet1 returns "57", not "1"
My actual functions are more complex, but this simple example illustrates the anomaly. I can jump through some hoops using .Address and string manipulations, but seems to me I should get the actual first cell of the spilled range on Sheet2...