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.