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
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