How to use Choose function in VBA?

The Choose function in VBA (Visual Basic for Applications) is used to select and return a value from a list of arguments based on a given index number. The syntax of the Choose function is:

Choose(index, choice1, choice2, ..., choiceN)

Here’s how to use it:

  • Index: This is a numeric expression that specifies which of the subsequent arguments to return. The first choice corresponds to an index of 1, the second choice to an index of 2, and so on. If the index is less than 1 or greater than the number of choices provided, the Choose function returns Null.
  • choice1, choice2, …, choiceN: These are the values from which the function chooses. You can list as many choices as you need.

Example Usage

Dim result As Variant
result = Choose(2, "Apple", "Banana", "Cherry")
' This would return "Banana" because it is the second choice

In this example, Choose returns “Banana” since the index is 2, and “Banana” is the second item in the list of choices.

Points to Remember

  • The Choose function is particularly useful when you have a limited, fixed set of possible values and you want to select one based on a numeric index.
  • Be cautious with the index value. If it’s not an integer between 1 and the number of choices, the function will return Null.
  • This function can be used to replace multiple If…ElseIf statements in some cases, making the code more concise and readable.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *