How to Use a Case Switch in VBA Excel

Write Visual Basic code in Excel.

A well written computer program can save time to the developer and the programmers that maintain it in the future. In VBA you can use the Select Case statement as an alternative to using "ElseIf" in "If...Then...Else" statements. A program becomes difficult to read when you have many "If...Then...Else" statements and may also run slower than if you use Select Case. A Select Case statement evaluates the expression only once while the "If...Else...Then" statements need to evaluate each step before executing code.

Instructions

1. Click the "Developer" tab, click "Visual Basic," and click the "Insert" menu. Click "Module" to insert a new code module.

2. Start by creating a sub procedure using the following code:

   Private Sub CaseSwitch()

3. Create three variables by typing the following lines of code:

   Dim employeePerf As Integer

   Dim salary As Currency

   Dim Bonus As Currency

4. Define values for the salary and employeePerf variables:

   salary = 75000

   employeePerf = 3

5. Use the select case statement to determine the bonus amount based on the performance of the employee and their base salary.

   Select Case employeePerf

   Case 1

   Bonus = salary * 0.1

   Case 2, 3

   Bonus = salary * 0.09

   Case 4 To 6

   Bonus = salary * 0.07

   Case Is > 8

   Bonus = 100

   Case Else

   Bonus = 0

   End Select

6. Display the bonus amount using the Immediate window.

   Debug.Print Bonus

7. End the procedure by typing "End Sub." Press "F5" to run the code.

References

• MSDN: Using Select Case Statements