+ Reply to Thread
Results 1 to 19 of 19

UDF For spill range(implicit intersection)

  1. #1
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    UDF For spill range(implicit intersection)

    I read in this new excel feature
    https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/
    For example
    In a1:a10 is range
    While type Function in b1
    Then dinamic array spill to b1:b10 but not using CSE
    Is possible UDF/Function can do that ?

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF For spill range(implicit intersection)

    I don't have access to the newest version of Excel, so I don't know. It should be simple to test if you have access to the latest version. Something as mind-numbingly simply as
    Please Login or Register  to view this content.
    and enter =testspill() into a blank cell.

    Your profile says you are using 2013. If you are waiting on the answer to this question to decide to upgrade, then maybe someone here with the latest version can test for you.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: UDF For spill range(implicit intersection)

    Excel 2016 64bit returns "1" with no resultant spill range -

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF For spill range(implicit intersection)

    Thanks for trying, nigelog. According to the OP's link, this feature may still be in "early release" mode where it is only available to "Office Insiders" with the appropriate subscription to 365. That may be the only difficult part of testing this, is finding someone with the right subscription(s).

    Editorial aside -- One of the reasons I am considering switching to other spreadsheet applications. I don't need a spreadsheet that hides its newest features behind subscription walls.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF For spill range(implicit intersection)

    Is possible UDF/Function can do that ?
    I don't have Office365 to test, but that would be a big change to VBA to allow UDFs to write outside the cells in which they appear.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF For spill range(implicit intersection)

    It seems like it is a big change for built in functions as well, since they can apparently take advantage of this new Spill feature. The OP's link includes an example with the FREQUENCY() function (Link included here sinc the forum did not parse the link in the OP https://www.excelcampus.com/function...-spill-ranges/ ).

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF For spill range(implicit intersection)

    Right. I just think VBA is Microsoft's redheaded stepchild.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF For spill range(implicit intersection)

    It probably depends on whether it is something that the function must do (Does the FREQUENCY() function need to look at the cell it is entered into and decide what to do with its output. "I am outputting an array, so value1 goes here, value2 goes here, etc.") or is it something that Excel does with any function output ("this function is outputting an array, so I will put the 1st result here, 2nd result here, etc.) If it is something that the function must do, then you are probably right. Microsoft is not really supporting VBA and they may not have included a way for a VBA UDF to do this. If it is something that Excel handles with any function, then it may treat a VBA UDF like any other array function.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF For spill range(implicit intersection)

    I expect RomperStomper (RoryA) would know.

  10. #10
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241
    Quote Originally Posted by MrShorty View Post
    I don't have access to the newest version of Excel, so I don't know. It should be simple to test if you have access to the latest version. Something as mind-numbingly simply as
    Please Login or Register  to view this content.
    and enter =testspill() into a blank cell.

    Your profile says you are using 2013. If you are waiting on the answer to this question to decide to upgrade, then maybe someone here with the latest version can test for you.
    I am sorri i not edit my excel version
    I using excel 2016 64 bitt
    "ThankyouFor Attention * And Your Help!!"

  11. #11
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241
    Quote Originally Posted by MrShorty View Post
    I don't have access to the newest version of Excel, so I don't know. It should be simple to test if you have access to the latest version. Something as mind-numbingly simply as
    Please Login or Register  to view this content.
    and enter =testspill() into a blank cell.

    Your profile says you are using 2013. If you are waiting on the answer to this question to decide to upgrade, then maybe someone here with the latest version can test for you.
    My mean if in b1 =testspill
    Automatatic b1:b5 contain function
    Testspill without copy and function or not using CSE

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF For spill range(implicit intersection)

    If you only have 2016 without the 365 "insider" subscription, it looks like you will need to wait for this feature. As noted, this feature is only available to a select group with the right 365 subscription.

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: UDF For spill range(implicit intersection)

    FYI it does work with UDFs that return arrays - there is no special coding required.
    Rory

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF For spill range(implicit intersection)

    Thanks for that.

    Does the formula appear in the spill range, or do you just see the value?

    Suppose a volatile UDF returns an array of random size -- what happens when it recalculates?

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: UDF For spill range(implicit intersection)

    You see the formula, but greyed out as with regular functions.

    You just get a SPILL error with a volatile random size UDF - at least on a Mac. Haven't tested on Windows.

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: UDF For spill range(implicit intersection)

    Thanks for the rep chaps.

  17. #17
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: UDF For spill range(implicit intersection)

    Apparently we have full 365 Subscription and latest excel - but I don't see any additional features. How could I check further??

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: UDF For spill range(implicit intersection)

    Unless you're on the Insider release channel (probably only Insider Fast), which is unlikely to be the case at work, you won't have it.

  19. #19
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: UDF For spill range(implicit intersection)

    @rorya - makes sense...ta

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Intersection of 2 Perpendicular Lines - Apparent Error in Intersection Coordinates
    By Paddy_Bear in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-03-2018, 03:36 AM
  2. Excel VBA Interception and Implicit Intersection and VLookUp
    By Doc.AElstein in forum Tips and Tutorials
    Replies: 11
    Last Post: 03-14-2017, 08:42 PM
  3. Replies: 1
    Last Post: 07-16-2013, 06:41 PM
  4. VBA to find the range at the intersection of two values.
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2013, 06:10 PM
  5. Identify intersection in named range
    By Freddy K in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2009, 10:29 AM
  6. spill
    By april27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2006, 12:55 PM
  7. [SOLVED] Possible to chart data for dates implicit within a range?
    By Nechama in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-01-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1