Hey guys, welcome back. So in this video, we're going to discuss the Select case statement. So the Select case statement in Excel VBA can perform a similar task to the if statement. So it can perform the same task as the if statement, although it has some limitations. So the if statement is basically more flexible and can have more uses. So the structure of the Select case statement is basically select case and then a test expression.

And that expression could be a value of a cell. It could be a value of a variable, it could be a formula, so like x plus y, select case x plus y. So I'm gonna test the value of x plus y. It could be also the value of a property for example, so select case a certain test expression, and then we're going to have some cases some different cases. So case condition one. So we're going to have some conditions that we're going to test against that test expression and condition basically is the value being one or the value being two, or it can be also the value being 40 or 50 or 60.

Or it can be the value being between 10 and 15 as well and we'll see how to perform that or it could be the value being less than 10. For example, all these are possible conditions that we can put and we will see how we can apply these so if condition one is met, then code one gets executed or whatever code under condition one gets executed. If that is not met, we go and test against condition to if the condition is true, then we're basically gonna execute the code under and we can put as many conditions as we want you know, so condition N means that you can put any number of conditions and under each condition have some code to execute. And we can also write case else and case else basically is If none of these conditions is true, we execute whatever under the case else.

So like the else in the if statement, and then we have an end select to indicate the end of our select case statement. And please note that if any of these conditions is met, then the rest of the conditions are not going to be tested. So if we're testing and condition one gets met, then we're not going to test condition two or three. And we're not going to execute the case else. Of course, we're just going to execute code one and then go to the end select and under SELECT statement. So that is similar to the if then else if statement, if any of the conditions gets met, we're basically going to exit the if then else if structure.

So let's see the case statement in action. Okay, so let's build a case statement together. So basically, let's say I'm going to put range D to value is equal to two. And then we're going to do a select case statement. So select case and then range D to value. So after the Select case, I need to put whatever expression I need to test, which in this case, I'm testing the value of range D two, and then I'm going to put the end SELECT statement here, so as not to forget it, and then we would put our tests or our conditions, so case two, so in case the value is equal to two message box two, and we could have also case less than seven message box less than seven case 40 comma 50, comma 60. message box 40 or 50, or 60.

Case 12. To 15 message box between 12 and 15. Case else message box Something else. Okay, so let's try to execute that select case statement here. So if you go through that step by step, so first of all, we're going to put the value of cell D two to be equal to two, you can see here, it's been given the value of two, and then we're going to test the value of cell D two. And then we're going to execute that condition here, case two.

So we're actually going to check if the value of cell D two is two. And that is actually going to be the case. So we're going to get a message box two. And you can see here that we've jumped straight away to the end select and we've not tested any other conditions so and select and sub. Let's say we're going to change the value of d two to be equal to five, for example, and let's test our conditions here. So basically, we're going to put the value is equal to five we're going to test the value here.

With if we test to, that test is going to be false. And then we're going to test case is less than seven. So we're going to get a message box less than seven, and then we jump to the end, select and then and sub. So as you could see here, we test each statement and when we find that one of them is true, we're actually gonna go straight away to the end select after executing the code under that particular case statement. And please note that actually, if you want to do the test for less than seven, you don't have to write case is less than seven, you could write case less than seven or less than eight or whatever. And then excel vba were actually correct your code automatically understand that you want to test if the value is less than seven and it will add the is by itself so I haven't written the is although you can write it of course, but you don't have to memorize or Remember to write it.

Now we're gonna do the third test here or case 40 comma 50. On 60, because this test is basically another way of testing, and it means that the value is either 40 or 50, or 60. And I've actually deliberately tried to show you different ways of performing the test. So here I'm performing a test for the cell D to being equal to a certain value than here being less than certain value. And here being one of three values, for example, and here being between some values. So I'm trying to show you different flavors of writing the case statement or different ways to do different tests.

So here, if we put the value of d to be equals to 40, for example, and try to go through the code step by step, you'll see here that the tests were 40 comma 50, comma 60, is going to go through and we're going to get the message box and select and sub and also if we try to put four point five, the test, the last one here between 12 to 15 is actually going to work because it can actually detect decimals as well. So the number doesn't have to be an integer. Okay, so it says no between 12 and 15. And if the value does not meet any of these conditions, of course, we're going to go to the case else. So for example, if we put it as 52, for example, we're actually going to go to the case else something else. One more thing I want to tell you about the case statement is that the origin and logicals cannot work with the case statement.

So this is why it is a bit limited than the if statement. So if we replaced the value of d to be 40, for example, and then we replaced 40 comma 50, comma 60, with 40 or 50, or 60, and we try the test. The case statement will not work as you can see here, it's pass that test, then it's done the one four case else, because we're not able to use that syntax with the case statement. Also, if we try to use for example greater than 39 and less than 41, for example, this will actually give us an error. So the case statement does not work with the or and and logical. So please bear that in mind.

So that's it for the case statement. It's basically a way of performing tests on a certain expression and taking actions based on these tests being true. So thank you guys for watching this video and I'll see on the next one