edit: solution - see original question below
When passing arrays like {1,2,3} to UDF, I found two things to consider:
locale - see answer 1. List separator on the German system (usually) - ";" therefore I need to use {1 ; 2 ; 3}.
the passed array is displayed as a two-dimensional array inside the function. Therefore, the nth element should be targeted as myArray (n, 1 ). Despite this, I gave #VALUE! error.
So a short “select case” -UDF might look like this:
Function SelCase(a1, a2, a3) For i = 1 To UBound(a2) If a2(i, 1) = a1 Then SelCase = a3(i, 1) Next End Function
called (German language!):
=SelCase(A1;{1;2;3};{"a";"b";"c"})
giving "a", "b" or "c" as a result, depending on A1, has 1, 2 or 3.
A more detailed "select case" -UDF is found here .
original question:
I would like to pass the array as {1,2,3,4} to a user-defined function in Excel 2002 - and I cannot find a way to do this.
I use the German version, so "," is my decimal separator, and it also separates the values in a horizontal (1D-) array - edit: this is wrong - , whereas ";" separates arguments in functions called formulas from a worksheet, and also allocates values in vertical (1D-) arrays - as far as I know.
I tried something like
Function test(myArray) Function test(myArray())
with something like
=test({1,2,3,4}) {=test({1,2,3,4})} (with ctrl+shift+enter)
in my worksheet, but Excel always asks me to correct my formula as "= test ({1,234})", which is not what I want.
If I try something like
=test({1;2;3;4}) =test(1,2,3,4) =test(1;2;3;4) <- ok this would be for paramArray
as the formula in the worksheet, I get #VALUE! error.
I cannot use paramArray because in the end I will have to pass two arrays (with a variable size) and one value in the form of three arguments. What syntax do I need in a sheet and in VBA to pass an array (which is not defined as a range)?
Thank you in advance! Martin Lindenlauf
change
What I'm trying to build is an abbreviated UDF for the "Select Case", for example:
Function SelCase() As Variant a1 = "b" a2 = Array("a","b","c") a3 = Array("e1","e2","e3") For i = 0 To UBound(a2) If a2(i) = a1 Then SelCase = a3(i) Next End Function
with a1, a2, a3, not defined inside the function, but passed by the function call, for example
=SelCase(A1;{"a","b","c"};{"e1","e2","e3"})
giving "e1" ... "e3" depending on A1 = "a", "b" or "c".
I could implement this with CHOOSE () and MATCH (), but I do it very often → as if to have a nice “short version” and by the way. I would like to understand what I am doing wrong with arrays and UDF ... Thanks.
change 2:
I found a working approach for "select case UDF" here . The general question remains: how to pass an array in the form of {1,2,3} -notation in UDF (if possible).