Consulting

Results 1 to 5 of 5

Thread: UDF returning multiple values

  1. #1
    VBAX Regular
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    67
    Location

    UDF returning multiple values

    Hi Folks

    I'm trying to write a user defined function that returns multiple boolean values that can then be used with AND OR worksheetfunctions.

    For example (WS):

    = KRITERIUM(A1;"x";A2;"x") should return (-1;-1) assuming that both cells A1 and A2 contain "x".

    = AND(KRITERIUM(A1;"x";A2;"x") should then return TRUE like writing =AND(-1;-1)

    However the problem is that it returns =AND("-1;-1") which results with an #VALUE error.

    FYI the function is defined as Public Function KRITERIUM(x as Variant, y as Variant, ....) as Variant

    Here is the line that assigns the return value (VBA): KRITERIUM = blnKriterium1 & "; " & blnKriterium2



    I'm now trying a workaround: instead of 0 or -1 I set the return values to "TRUE" and "FALSE" (eg. = AND("TRUE"; "TRUE") is TRUE)

    However, it returns now = AND("TRUE; TRUE") so 2 quotation marks are still missing. If I use double quotes in VBA like so:

    KRITERIUM = blnKriterium1 & """; """ & blnKriterium2 it returns =AND("TRUE"";"" TRUE") and therefore an error

    What's weird is that while debugging it shows KRITERIUM = "TRUE"; "TRUE" but not on the WS


    Any ideas are much appreciated
    Last edited by nikki333; 06-10-2018 at 09:56 AM.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,913
    Location
    Not too sure why you can to do it that way -- seems a little round about

    This might give you some ideas

    The formula is array-entered - select the 2 cells and use control-shift-enter - Excel will add the braces for you

    Capture.JPG

    or you can use it directly in a WS function


    Capture.JPG


    Option Explicit
    
    Function KRITERIUM(r1 As Range, v1 As Variant, r2 As Range, v2 As Variant) As Variant
        Dim v(1 To 2) As Boolean
        
        v(1) = (r1.Value = v1)
        v(2) = (r2.Value = v2)
        
        KRITERIUM = v
        
    End Function
    Last edited by Paul_Hossler; 06-10-2018 at 10:10 AM. Reason: Add second picture
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    67
    Location
    Many thanks Paul, I don't completely understand the logic behind this, but it works

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,913
    Location
    The function returns a Variant containing an one dimension Array

    To enter an array onto a WS you select the group of cells and array-enter the formula (Excel has a number of array formulas built in)

    Since the function returns an array and something like AND() or OR() expects an array, both are happy
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    67
    Location
    Many thanks for your explanation!

Posting Permissions

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