EXCEL VBA SERIES – POST – 9 – Select Case

Excel Select Case
Spread the love

 

 

EXCEL VBA SERIES – POST – 9 – [ Switch Case ]:

In this blog post you are going to learn the following

  • When to use switch case statements

  • Advantages of switch case statements over​​ if-else

  • Update the contents of cells using switch case statements

You might be wondering switch case statements work more like an if else statement, well if you have more conditions to look out for then switch case statement is obviously the better choice​​ compared to if-else statement.​​ 

The main reasons for using a switch include improving clarity, by reducing otherwise repetitive coding, and also offering the potential for faster execution through easier compiler optimization

The compiler doesn't know if the order of evaluating the if-statements is important to you, and can't perform any optimizations there. You could be calling methods in the if-statements, influencing variables. With the switch-statement it knows that all clauses can be evaluated at the same time and can put them in whatever order is most efficient.

Let’s take the below example in the attached excel sheet and see how we can update the cell values in a worksheet based on certain conditions.​​ Here based on the dept id , we are going to update the values in deptname, salary and salary band.

 ​​ ​​ ​​​​ 

'Get the last row in the excel sheet

With ThisWorkbook.Sheets("starwars")

 ​​ ​​​​ LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

Debug.Print "LastRow:" & LastRow

 

'Iterate through the available rows in a loop

For i = 2 To LastRow

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'get the deptid value from the cell

 ​​ ​​ ​​​​ deptid = ThisWorkbook.Sheets("starwars").Cells(i, "C").Value

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'update dept name based on dept id

 ​​ ​​ ​​​​ Select Case deptid

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case 1, 5, 7, 9

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "D").Value = "Admin"

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case 2, 4, 6, 10

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "D").Value = "Finance"

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case 3, 8, 11, 15

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "D").Value = "IT"

 ​​ ​​ ​​ ​​ ​​ ​​​​ 'you can use "To" for​​ continuous​​ numbers

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case 12 To 14

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "D").Value = "HR"

 ​​ ​​ ​​​​ End Select

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'get the deptname value from the cell

 ​​ ​​ ​​​​ deptname = ThisWorkbook.Sheets("starwars").Cells(i, "D").Value

 ​​ ​​ ​​​​ 

 ​​ ​​​​ 'update salary based on deptname

 ​​ ​​ ​​​​ Select Case deptname

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case "Admin"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "E").Value = 22000

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case "Finance"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "E").Value = 34000

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case "IT"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "E").Value = 55000

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case "HR"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "E").Value = 42000

 ​​ ​​ ​​​​ End Select

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'get the salary value from the cell

 ​​ ​​ ​​​​ salary = ThisWorkbook.Sheets("starwars").Cells(i, "E").Value

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'Update salary band based on salary value

 ​​ ​​ ​​​​ Select Case salary

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case Is >= 50000

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "F").Value = "A"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ 'Switch case multiple conditions, you cannot use "And", "&&" here , instead you use a comma

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case Is < 50000, Is >= 40000

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "F").Value = "B"

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case Is < 40000, Is >= 30000

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "F").Value = "C"

 ​​ ​​ ​​ ​​ ​​ ​​​​ Case Is < 30000, Is >= 20000

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ThisWorkbook.Sheets("starwars").Cells(i, "F").Value = "D"

 ​​ ​​ ​​​​ End Select

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 

Next

 

​​ These are the ways in which you can use the select case conditions, Hope you enjoyed this post, I have attached the excel sheet for your reference, you can see the download link after you logon to this website using your social login.​​ 

Please post your comments below , Please Like and follow us on Facebook.​​ https://www.facebook.com/gadoth

==================================

==================================

Related posts

Leave a Comment