title: Excel Select Case?
body: <p>i want to create the "cases" formula for excel to simulate Select case behavior (with multiple arguments and else optional).
  If A1 and A2 are excel cells, this is the goal:</p>
  
  <pre><code>A1 Case:     A2 Formula:                                                                   A2 Result
  5            cases({A1&gt;5,"greather than 5"}, {A1&lt;5, "less than 5"},{else,"equal to 5"})    equal to 5   
  Hi           cases({A1="","there is nothing"},{else,A1})                                   Hi
  1024         cases({5&lt;A1&lt;=10,10},{11&lt;=A1&lt;100,100},{A1&gt;100,1000})                           1000
  12           cases({A1=1 to 9, "digit"}, {A1=11|22|33|44|55|66|77|88|99, "11 multiple"})   (empty) 
  60           cases({A1=1 to 49|51 to 99,"not 50"})                                         not 50
  </code></pre>
  
  <p>If it could, It must accept excel formulas or vba code, to make an operation over the cell before take a case, i.g.  </p>
  
  <pre><code>cases({len(A1)&lt;7, "too short"},{else,"good length"})
  </code></pre>
  
  <p>If it could, it must accept to or more cells to evaluate, i.g. </p>
  
  <p>if A2=A3=A4=A5=1 and A1=2, A6="one", A7="two"</p>
  
  <pre><code>cases(A1!=A2|A3|A4|A5, A6}, {else,A7}) will produce "two"
  </code></pre>
  
  <p>By the way, | means or, != means different</p>
  
  <p>Any help? </p>
  
  <hr>
  
  <p>I'm grateful.</p>
  
  <p>What I could write was this:</p>
  
  <pre><code>Public Function arr(ParamArray args())  'Your function, thanks
      arr = args
  End Function
  
  Public Function cases(arg, arg2)  'I don't know how to do it better
      With Application.WorksheetFunction
          cases = .Choose(.Match(True, arg, 0), arg2)
      End With
  End Function
  </code></pre>
  
  <p>I call the function in this way</p>
  
  <pre><code>=cases(arr(A1&gt;5, A1&lt;5, A1=5),arr( "gt 5", "lt 5", "eq 5"))
  </code></pre>
  
  <p>And i can't get the goal, it just works for the first condition, A1>5. </p>
  
  <p>I fixed it using a for, but i think it's not elegant like your suggestion:</p>
  
  <pre><code>Function selectCases(cases, actions)
      For i = 1 To UBound(cases)
          If cases(i) = True Then
              selectCases = actions(i)
              Exit Function
          End If
      Next
  End Function
  </code></pre>
  
  <hr>
  
  <p>When i call the function:</p>
  
  <pre><code>=selectCases(arr(A1&gt;5, A1&lt;5, A1=5),arr( "gt 5", "lt 5", "eq 5"))
  </code></pre>
  
  <p>It works.</p>
  
  <p>Thanks for all.</p>
  
  <hr>
  
  <p>After work a little, finally i get a excel select case, closer what i want at first.</p>
  
  <pre><code>Function cases(ParamArray casesList())
      'Check all arguments in list by pairs (case, action),
      'case is 2n element
      'action is 2n+1 element
      'if 2n element is not a test or case, then it's like the "otherwise action"
      For i = 0 To UBound(casesList) Step 2
          'if case checks
          If casesList(i) = True Then
              'then take action
              cases = casesList(i + 1)
              Exit Function
          ElseIf casesList(i) &lt;&gt; False Then
              'when the element is not a case (a boolean value),
              'then take the element.
              'It works like else sentence
              cases = casesList(i)
              Exit Function
          End If
      Next
  End Function
  </code></pre>
  
  <p>When A1=5 and I call:</p>
  
  <pre><code>=cases(A1&gt;5, "gt 5",A1&lt;5, "lt 5","eq 5")
  </code></pre>
  
  <p>It can be read in this way: When A1 greater than 5, then choose "gt 5", but when A1 less than 5, then choose "lt 5", otherwise choose "eq 5". After run it, It matches with "eq 5"</p>
  
  <p>Thank you, it was exciting and truly educative!</p>