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

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

Attachment 22402

or you can use it directly in a WS function


Attachment 22404

Code:

---

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

---

Many thanks Paul, I don't completely understand the logic behind this, but it works :)

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

Many thanks for your explanation!