# Thread: UDF returning multiple values

1. ## 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  Reply With Quote

2. 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```  Reply With Quote

3. Many thanks Paul, I don't completely understand the logic behind this, but it works   Reply With Quote

4. 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  Reply With Quote

5. Many thanks for your explanation!  Reply With Quote

#### Posting Permissions

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