PDA

View Full Version : [SOLVED] UDF returning multiple values



nikki333
06-10-2018, 09:07 AM
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

Paul_Hossler
06-10-2018, 10:05 AM
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

22402

or you can use it directly in a WS function


22404




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

nikki333
06-10-2018, 01:20 PM
Many thanks Paul, I don't completely understand the logic behind this, but it works :)

Paul_Hossler
06-10-2018, 05:25 PM
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

nikki333
06-12-2018, 11:10 AM
Many thanks for your explanation!