The VBA Choose Function


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-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.

VBA Choose Function Example

' 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:

VBA Choose Function Error

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

VBA Run Time Error 13 Message Box