4

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

pdtcaskey
  • 242
  • 1
  • 9
  • I am unable to reproduce this: https://i.stack.imgur.com/0XU5H.png – BigBen Jul 21 '21 at 23:30
  • https://1drv.ms/x/s!Asb8Fbj8gOc2g4NuDZ1ohh3A3w6xOA?e=Hh3Cpo Here it is for me; it shows 57 vs 1. – pdtcaskey Jul 26 '21 at 02:38
  • Screenshot of results: https://1drv.ms/u/s!Asb8Fbj8gOc2g4NwgYsff8OYjLH3jg?e=jYupV1 – pdtcaskey Jul 26 '21 at 02:48
  • Perhaps a bug that's been fixed... refreshing that cell gives me the correct response of 1. Version 2108 (Build 14312.20008). – BigBen Jul 26 '21 at 02:50
  • Microsoft® Excel® for Microsoft 365 MSO (16.0.14131.20326) 64-bit. Still erroneous for me. (2106 build) – pdtcaskey Jul 26 '21 at 02:52
  • You could always try signing up for Office Insider to see if a later release fixes the issue. – BigBen Jul 26 '21 at 02:54
  • Will need to check with IT dept before I try, but thanks much for taking the time to respond, regardless!! – pdtcaskey Jul 26 '21 at 02:55
  • Same error reported [here](https://answers.microsoft.com/en-us/msoffice/forum/all/spillingtorange-returning-incorrect-results-am-i/85dce3e6-33b5-4bd1-834b-82bfb6e49ad6). – BigBen Mar 29 '22 at 18:21

0 Answers0