Select Case (Excel VBA)
Select Case Statement lets you execute several group of statements based on value of an expression. It works like CHOOSE Function in Excel.
Syntax for Select Case Statement is:
*Please note that I don’t claim that code examples are the most efficient or best for the jobs that theya re written for. They are presented for explainatoy purposes only.
Select [ Case ] testexpression
[ Case expressionlist
[ statements ] ]
[ Case Else
[ elsestatements ] ]
End Select
Here is an example application of Select Case Statement:
Sub SelectCase_Example()
‘ This macro determines salary increase based on
‘ current salary and working years
Dim WorkingYears As Variant
Dim Salary As Long
Dim Increase As Double
Salary = InputBox(“Enter Salary: “)
WorkingYears = InputBox(“Enter Working Years: “)
Select Case WorkingYears
Case 0 To 2
Increase = Salary * 0.05
Case 3 To 5
Increase = Salary * 0.075
Case 6 To 10
Increase = Salary * 0.1
Case Is > 10
Increase = Salary * 0.125
End Select
MsgBox (“Increase is: ” & Increase)
End Sub
If you input 2000 as salary and 4 as working years, it will calculate 75 as your increase. Likewise if you input 2000 as salary and 9 as working years, it will calculate 200 as your increase.
For more Excel VBA related tutorials, you can visit VBA Category.