VBA Choose Function

The VBA Choose function is used to select and return a specific value from a list or array of values. It allows the user to input an index number and retrieve the corresponding value from a list or array. This function is particularly useful when working with large datasets or when performing calculations based on different conditions.

VBA Choose Function – Purpose, Syntax and Arguments

Syntax:

Choose(index, choice1, choice2, …, choice_n)

Arguments:

• index: This is a required argument and represents the index of the value that needs to be returned. It can be any numeric value between 1 and 254.
• choice1, choice2, …, choice_n: These arguments are also required and represent the list or array of values from which the function will return the selected value. There can be any number of choices specified, as long as they are separated by commas.

Example:

Suppose we have a list of employees and their corresponding salaries, and we want to retrieve the salary of the employee at index 3 from the list. We can use the Choose function in the following way:

Dim employeeSalaries As Variant
employeeSalaries = Array(50000, 60000, 70000, 80000, 90000)
Dim selectedSalary As Variant
selectedSalary = Choose(3, employeeSalaries)
MsgBox "The salary of the selected employee is " & selectedSalary

In this example, the value of the variable ‘selectedSalary’ will be set to 70000, which is the third entry in the ’employeeSalaries’ array.

Remarks and Important Notes:

• The index argument for the Choose function is zero-based, meaning that the first value in the list has an index of 1, the second value has an index of 2, and so on.
• The Choose function can only work with lists or arrays that have a maximum of 255 values. If you have a larger dataset, you may need to consider using a different function to retrieve specific values.
• The ‘index’ argument can also be a variable, making the Choose function dynamic. This means you can change the index value at runtime, and the function will return the corresponding value from the list or array.

Understanding VBA Choose Function with Examples

VBA (Visual Basic for Applications) is a programming language that is used to automate and customize Microsoft applications such as Excel, Word, and PowerPoint. It allows users to create macros and functions, automate tasks, and perform complex calculations. One of the most useful functions in VBA is the Choose function, which is used to return a value from a list based on the index number provided. In this blog post, we will explore the Choose function in detail and provide examples to help you understand its usage.

Simple Use of Choose function

Imagine you have a list of fruits (Apple, Banana, Mango, Orange, Pineapple) and you want to return the third fruit from the list. You can achieve this using the Choose function as shown below:

fruit = Choose(3, "Apple", "Banana", "Mango", "Orange", "Pineapple")

1. The first argument (3) in the Choose function specifies the index number for the item you want to retrieve from the list.
2. The following arguments (“Apple”, “Banana”, “Mango”, “Orange”, “Pineapple”) are the list of items from which you want to return a specific value.

In this case, the value returned will be “Mango” since it is the third item in the list.

Using Variables as Arguments

The Choose function also allows you to use variables as arguments. This can be useful when you want to dynamically change the index number or list of items based on certain conditions. Let’s say you have a list of students (John, Sarah, Alex, Emily, James) and you want to return the fifth student from the list. However, the index number will depend on the number of students in the list. Here’s how you can achieve this using the Choose function:

numStudents = 5
student = Choose(numStudents, "John", "Sarah", "Alex", "Emily", "James")

In this case, the value returned will be “James” since it is the fifth item in the list.

Using Choose function in a Loop

The Choose function can also be used in a loop to iterate through a list of items and perform certain actions. Let’s say you have a list containing the names of different countries (USA, Canada, France, Japan, Brazil) and you want to print out each country name on a new line. Here’s how you can do it using the Choose function in a loop:

'countries' variable
'Assuming the list of countries is stored in the
For i = 1 To 5
country = Choose(i, countries)
Debug.Print country
Next i

1. The loop will iterate 5 times since there are 5 items in the list.
2. The Choose function will return the corresponding value for each index number (1, 2, 3, 4, 5) which will be stored in the ‘country’ variable.
3. The ‘Debug.Print’ statement will print out the value of the ‘country’ variable on a new line in the Immediate window.

The output will be:
USA
Canada
France
Japan
Brazil

Using Choose function with Arrays

Often, you may have a large list of items and it may not be feasible to include all of them as arguments in the Choose function. In such cases, you can use an array and pass it as an argument in the Choose function. The following example will illustrate this:

'colors' array
'Assuming the list of colors is stored in the
color = Choose(2, colors)

In this case, the value returned will be the second item in the ‘colors’ array. Suppose the array contains the following colors: Red, Blue, Green, Yellow, Orange. The value returned will be “Blue” since it is the second item in the array.

Using Choose function with Nested ‘IF’ statements

The Choose function can also be used in nested ‘IF’ statements to simplify the code and make it more readable. Let’s say you have a list of salespeople and you want to assign bonuses to each of them based on their sales. Here’s how you can do it using the Choose function:

sales = 50000
bonus = Choose(sales >= 50000, 500, Choose(sales >= 40000, 400, Choose(sales >= 30000, 300, Choose(sales >= 20000, 200, Choose(sales >= 10000, 100, 0)))))

In this case, the value returned will be 500 since the salesperson’s sales are equal to or above 50000. The nested ‘IF’ statements are evaluated from the innermost to the outermost and the Choose function returns the corresponding bonus amount based on the condition met.

Conclusion

The Choose function in VBA is a powerful tool that allows you to retrieve a value from a list of items based on the index number provided. It can be used in various scenarios such as retrieving values from a list, using variables and arrays as arguments, and in loops and nested ‘IF’ statements. Understanding the Choose function and its usage can significantly improve your VBA coding skills and help you automate tasks effectively. We hope this blog post has provided you with a better understanding of the Choose function and its capabilities. Happy coding!