The VBA Choose Function

Description

For a supplied index, the VBA Choose function selects the corresponding value from a list of arguments.

The syntax of the function is:

Choose( Index, [Choice-1], [Choice-2], ... )

Where the function arguments are:

Index	-	The index of the value that you want to return (must be between 1 and n, where n is the number of possible values).
[Choice-1], [Choice-2], ...	-	A list of possible values to be returned (depending on the value of Index).

If the supplied Index is less than 1, or is greater than the number of supplied choices, the Choose function returns the value Null.

								' Return specified values from a list of names.

  							Dim val1, val2, val3, val4

    						val1 = Choose( 1, "Mary", "Joseph", "Lucy", "Peter" )

    						' val1 is now equal to "Mary".

    						val2 = Choose( 2, "Mary", "Joseph", "Lucy", "Peter" )

    						' val2 is now equal to "Joseph".

    						val3 = Choose( 3, "Mary", "Joseph", "Lucy", "Peter" )

    						' val13 is now equal to "Lucy".

val4 = Choose( 4, "Mary", "Joseph", "Lucy", "Peter" )
' val4 is now equal to "Peter".

The above VBA code uses the Choose function to return a value from the list "Mary", "Joseph", "Lucy", "Peter".

The value of the Index argument determines which of the list items is returned:

When Index is set to 1, the Index function returns the first list item, "Mary";
When Index is set to 2, the Index function returns the second list item, "Joseph";
When Index is set to 3, the Index function returns the third list item, "Lucy";
When Index is set to 4, the Index function returns the fourth list item, "Peter".

If the Index argument to the Choose function cannot be interpreted as a numeric value, the function will return the error:

Run-time error '13': Type mismatch