Choose Function

---

Named Arguments

No

Syntax

Choose(index, item1[, item2, ...[,itemn]])

index

Use: Required

Data Type: Single

An expression that evaluates to the number of the item to choose from the list.

item1 - n

Use: Required

Data Type: Variant

A comma-delimited list of values from which to choose.

Return Value

A variant data type item chosen from the list; the data subtype is that of the chosen item.

Description

Programmatically selects an item from a predefined list of values (which are passed as parameters to the function) based on its ordinal position in the list. Using Choose is a simpler alternative to populating an array with fixed values.

Rules at a Glance

• The list of items is based from 1, rather than the more usual VB default base of 0.

• Because the list of values is a variant parameter array, you can mix data subtypes within the list; you aren't forced to use the same data subtype for each item in the list. For example, item1 can be a variant string, item2 a long integer, and item3 a floating point number.

• The list of values can't be expanded or contracted programmatically, but the items within the list can be generated dynamically by including the return value of a function call. For example:

  vChosenOption = Choose(iOption, vFunction1(), _
                  vFunction2(), vFunction3())

  
  

Example

Choose is useful for returning some result based on an option button selection. This is most straightforward with Visual Basic, which supports control arrays. For example:

Private Sub Option1_Click(Index As Integer)

Form1.BackColor = Choose(Index + 1, &HFF&, _
                         &HFF00&, &HFF0000)

End Sub

Within Office/VBA (which doesn't support control arrays), using the Choose function to handle the an option button selection is still useful:

Private Sub SetBackgroundColor(Index As Integer)

   UserForm1.BackColor = Choose(Index, &HFF&, &HFF00&, _
                                &HFF0000)

End Sub

Private Sub OptionButton1_Click()
   SetBackgroundColor 1
End Sub

Private Sub OptionButton2_Click()
   SetBackgroundColor 2
End Sub

Private Sub OptionButton3_Click()
   SetBackgroundColor 3
End Sub

Programming Tips and Gotchas

• Passing an index value that is either negative, zero, or greater than the number of items in the list returns a variant data subtype of Null without generating an error. Because the return value is a variant, you should check for it by calling the IsNull function, as shown below; otherwise, you'll generate an "Invalid use of Null" error when you try to use the result:

  Dim z As Variant
  z = Choose(0, "Eany", "Meany", "Miney", "Mo")
  If IsNull(z) Then 
      MsgBox "bad choice"
  Else
      MsgBox z
  End If

  
  

• Strangely, the data type of index is Single, and not an Integer as you may have expected (though it's hard to believe anyone would hand-code a list of more than 32,767 items to warrant a Long!). Does this mean you can choose the 1.234th item in the list? No, of course not; the index number is automatically rounded down to a whole number prior to being used to select an item.

• One word of warning when using the return value of functions to populate the Choose list: All items in the list are evaluated. This means that every call to the Choose function generates calls to each of the functions listed, which in turn means that you must be sure that each function has the ability to be successfully executed without causing undesirable side effects each time you call the Choose function.

• You can save memory and create more efficient and self documenting code by using the Choose function in preference to creating an array and populating it with fixed values each time the program executes. As the following example illustrates, you can turn several lines of code into one:

  Dim vMyArray(3)
  vMyArray(1) = "This"
  vMyarray(2) = "That"
  vMyArray(3) = "The Other"
  ...
  Sub chooseFromArray(iIndex as Integer)
      vResult = vMyArray(iIndex)
  End Sub
  
  Sub chooseFromChoose(sglIndex as Single)
      vResult = Choose(sglIndex, "This", "That", "The Other")
  End Sub

  
  

See Also

IIf Function, Select Case Statement, Switch Statement