title: Excel: Passing Array to User Defined Function (VBA)
body: <p><strong>edit: solution</strong> - see original question below</p>
  
  <p>In passing arrays like {1,2,3} to a UDF I found two things to be considered:</p>
  
  <ul>
  <li><p>the locale - see answer 1. The list separator on a german system (usually) is ";" therefore I need to use {1 <strong>;</strong> 2 <strong>;</strong> 3}.</p></li>
  <li><p>the passed array appears as a 2-dimensional array within the function. Therefore it's n-th element must be targeted as myArray(n, <strong>1</strong>). Disregarding this gave me the #VALUE! error.</p></li>
  </ul>
  
  <p>Thus, a short "select case"-UDF may look like this:</p>
  
  <pre><code>Function SelCase(a1, a2, a3)
      For i = 1 To UBound(a2)
          If a2(i, 1) = a1 Then SelCase = a3(i, 1)
      Next
  End Function
  </code></pre>
  
  <p>called like (german locale!):</p>
  
  <pre><code>=SelCase(A1;{1;2;3};{"a";"b";"c"})
  </code></pre>
  
  <p>giving "a", "b", or "c" in result depending on A1 holds 1, 2 or 3.</p>
  
  <p>A more elaborated "select case"-UDF is found <a href="https://stackoverflow.com/questions/4716382/excel-select-case/4719706#4719706">here</a>.</p>
  
  <hr>
  
  <p><strong>original question:</strong></p>
  
  <p>I'd like to pass an array like {1,2,3,4} to a user defined function in Excel 2002 - 
  and don't manage to find the way to do this.</p>
  
  <p>I'm using a german version, so "," is my decimal separator and also separates values in an horizontal (1D-)array <strong>- edit: this is wrong -</strong> , wheras ";" separates arguments in functions called in formulas from sheet and also seprates values in vertical (1D-)arrays - as far as I know.</p>
  
  <p>I tried something like</p>
  
  <pre><code>Function test(myArray)
  Function test(myArray())
  </code></pre>
  
  <p>with something like</p>
  
  <pre><code>=test({1,2,3,4})
  {=test({1,2,3,4})} (with ctrl+shift+enter)
  </code></pre>
  
  <p>in my sheet, but Excel alway asks me to correct my formula like "=test({1,234})" which is not what I want.</p>
  
  <p>If I try something like</p>
  
  <pre><code>=test({1;2;3;4})
  =test(1,2,3,4)
  =test(1;2;3;4) &lt;- ok this would be for paramArray
  </code></pre>
  
  <p>as formula in the sheet I get an #VALUE! error.</p>
  
  <p>I can't use paramArray 'cause in the end I'll have to pass two arrays (with variable size) and one single value as 3 arguments. What syntax do I need in the sheet and in VBA to pass an array (which is not defined as a range)? </p>
  
  <p>Thank you in advance!
  Martin Lindenlauf</p>
  
  <p><strong>edit:</strong></p>
  
  <p>What I'm trying to build is a shorthand UDF for "Select Case", like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>with a1, a2, a3 not defined within the function but passed by function call like</p>
  
  <pre><code>=SelCase(A1;{"a","b","c"};{"e1","e2","e3"})
  </code></pre>
  
  <p>giving "e1"..."e3" depending on A1 = "a", "b" or "c".</p>
  
  <p>I could realize this with CHOOSE() and MATCH() but I neet it very often -> like to have a nice "short version", and btw. I'd like to understand what I'm doing wrong with arrays and UDFs... Thanks.</p>
  
  <p><strong>edit 2:</strong></p>
  
  <p>I found a working approach for "select case UDF" <a href="https://stackoverflow.com/questions/4716382/excel-select-case/4719706#4719706">here</a>. The general question remains: how to pass an array in a kind of {1,2,3}-notation to an UDF (if possible).</p>