+ Reply to Thread
Results 1 to 19 of 19

Sorting UDF for use in array formulas

  1. #1
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Sorting UDF for use in array formulas

    Well, the title says it all and so does the attached workbook.

    I welcome all solutions, UDF's as well as monsterarray formulas but I'm more curious about the UDF.

    As for the sorting algorithm itself I prefer short code over long code (hello bubblesort! ) because I have small data sets. Again I'm open for any suggestions.

    VBA code for sorting can be found all over the internet, my problem is that I haven't been able to successfully make a UDF implementation of it.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    How about
    Please Login or Register  to view this content.
    Used like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Sorting UDF for use in array formulas

    Or :
    Please Login or Register  to view this content.
    and used in a cell as an array formula (confirmed by Control Shift Enter) like this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    Very nice solutions, I'm impressed! I tested them and both works of course.

    I did not know about ArrayList, pretty cool stuff: https://excelmacromastery.com/vba-arraylist/


    karedog, what is the purpose of your first If?
    Is it just to skip the sorting if param is a single item?

    I copied a little bit from both of you so my code looks like this:

    Please Login or Register  to view this content.

    However, I was a bit curious about bubblesort as well so I tried to make my own UDF based on yours. Of course it didn't work.
    Why doesn't it work? Code looks like this:
    https://bettersolutions.com/vba/arra...ubble-sort.htm
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    The array being passed to the function is a 2d array, also as you are passing letters not numbers Temp needs to be String or variant
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    Thanks! I tried it and it works when I use it in TEXTJOIN as above.

    However, when I try to use it in LOOKUP it doesn't work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    That's because you're passing it a range not an array of values.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    How would one go about to make it accept either an array of values or a range? Like the native Excel functions.

  9. #9
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Sorting UDF for use in array formulas

    One important point is that; in order to work with System.Collections object, which does not depend on what version of Excel you are using .Net Framework 3.5 should available on the PC.

    If not, the code will throw an error.

    .
    Last edited by Haluk; 07-13-2019 at 04:49 PM.

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    How about
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    Perfect! Thanks!

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    You're welcome & thanks for the feedback

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    I got stuck playing around with this. I can't make the arraylist UDF work on ranges.
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    The array list returns a 1d array, whereas you need a 2d array.
    Try
    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    Jeez how do you know all this?
    Works now, thanks!

  16. #16
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Sorting UDF for use in array formulas

    You're welcome & thanks for the feedback.

  17. #17
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    I have used this UDF for one application now and it worked well but the output was in one cell.

    Now I tried to use it as a multicell arrayformula and it too worked fine.

    But when I tried to use it as a multicell arrayformula within a Table it doesn't work. Why? How do I make it work?
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Sorting UDF for use in array formulas

    See attached.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Sorting UDF for use in array formulas

    Very impressive jindon, thank you! This is what I needed.
    Of course I don't understand how or why

+ 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] Array reference in a cell: difference using or not array formulas
    By RiccardoS89 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2019, 12:07 PM
  2. Replies: 3
    Last Post: 01-01-2016, 03:44 PM
  3. Question about using Evaluate with an array of formulas not an array formula
    By Mr_Bill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2014, 03:26 PM
  4. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  5. Change all regular formulas into array formulas
    By sans in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-18-2012, 11:28 AM
  6. Replies: 6
    Last Post: 12-30-2008, 06:52 AM

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