Get a month of TabletWise Pro for free! Click here to redeem

Case Statements Part 2 - Nested Case Statements

6 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$201.42
You save:  A$57.55
List Price:  ৳11,885.38
You save:  ৳3,396.06
List Price:  CA$190.22
You save:  CA$54.35
CHF 94.11
List Price:  CHF 131.76
You save:  CHF 37.64
List Price:  kr922.75
You save:  kr263.66
List Price:  €123.88
You save:  €35.39
List Price:  £110.90
You save:  £31.68
List Price:  HK$1,085.28
You save:  HK$310.10
List Price:  ₹10,526.61
You save:  ₹3,007.82
List Price:  RM597.19
You save:  RM170.64
List Price:  ₦54,241
You save:  ₦15,498.53
List Price:  kr1,319.67
You save:  kr377.07
List Price:  NZ$212.70
You save:  NZ$60.77
List Price:  ₱6,925.45
You save:  ₱1,978.84
List Price:  ₨23,257.07
You save:  ₨6,645.35
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,386.84
You save:  ฿1,253.47
List Price:  ₺960.89
You save:  ₺274.56
List Price:  R2,348.19
You save:  R670.96
Already have an account? Log In


Hey guys, welcome back. So in this video we're going to discuss the nested case statements. So how to nest case statements inside each other and actually have the same idea as nested ifs as well. So we're going to illustrate nesting case statements using a game here that I've created. So basically this game is based on the fact that if you live in the Northern Hemisphere or the northern half of the earth, your winter is going to be in December, January and February, your spring is going to be in March, April and May. And your summer is going to be June, July and August and your autumn will be in September, October or November, right.

That's if you live in the Northern Hemisphere or the northern half of the earth, like myself, I live in Egypt for example. And also if you live in the southern hemisphere or the southern half of the earth, like if you live in Australia Yeah, for example, your summer is going to be in December, January and February, your Autumn is going to be in March, April and May, your winter is going to be in June, July and August. And your spring is going to be in September, October and November. Right. So here I've got some formulas here that are generating a random date and a random value for the hemisphere, either north or south. And we're actually gonna have a macro here that has nested case statements where as you can see here, we get two variables at the beginning variable called month in which is actually going to hold the integer variable the month, so it's going to get the month from the date, okay, and this value is going to be an integer, like one or two, or three or four, depending on the month.

Okay, and it's going to have a maximum value of 12, of course, and that's why we don't need to make the data type as an integer because the maximum value we're going to get is 20. And the bite can hold up to 255. And we're going to another variable here called hemisphere, it's stuck is a string and is going to hold the value of the hemisphere from cell B to so the month is going to get the value of the month integer from cell A to by having the month function applied to the date. And the month function will produce the integer value of the month. And then we're going to have a case statement here select case hemisphere. So we're going to test the value of the variable hemisphere.

If its value is north, we're actually going to nest another case statement in there. So select case, the value of month in so we're going to do another test we're going to nest another test inside the test for the hemisphere. And we're going to basically check for the value for the integer and display the message box that corresponds to that month in the Northern Hemisphere. And of course, if the value is not north, then we're gonna check on the south And it's going to be either north or south. So it's going to go south here, and then it's going to test the value of the month and, and display the message box corresponding to the season of the year. And notice here how I'm indenting my case statements here, so select case hemisphere, and then I'm indenting, the case north and then indenting.

Again, select case month. And I'm indenting the code here for the sake of readability, it doesn't have any effect on the execution of the code. But it's just for readability to be able to read the code to understand that this case here, this test is going to be inside the first select case and then the second select case is actually inside this case test. Okay, just the same idea as a nested if we're indenting our code for readability so currently We've got the date Third of May 2016. And the hemisphere is north. So that should produce the spring.

So we're going to go through the code step by step here. First of all, we're going to select our worksheet, nested case statement that we're working on. Okay, so just we're making sure to select the correct worksheet, because we've got two worksheets in this workbook. And then we're going to input the value for the month into here, and we're going to get a five because we're in May, and then the hemisphere, we're going to get a north. And then we're going to test the value for hemisphere case north. Is it North?

Yes, it is. We're going to go ahead and execute the nested case statement here the case statement that is nested inside the first case statement. And we're going to do our tests for the seasons and we're going to get spring and then so we've got spring we jump straight away to the intellect and this intellect is actually the unselect for this Select case statement. Okay, this is why as you can see here they are indented to the same level. And this indicates that this and select is for this select case and you need to put an end select for every select case. So here because we've got one, select case to select case, says three, so we get three select case statements, we need to have three corresponding and select.

So here's an unselect. Here's an unselect that closes this one here. And here is that and select that closes the first one for the hemisphere. Okay, so it's the same idea as nesting if statements. So then select and select. Now these formulas actually produce random values whenever the workbook recalculates.

So can actually recalculate my workbook here, calculate now and we get different value for South and for the date. So if we do the test again here, we're going to test for a hemisphere case. No No, it's not north. So we're going to go south here, and it's going to be in April. So that is going to produce autumn and then select and then select. Ok.

So I hope you got the idea for the nested case statements. Thank you very much for watching this video guys and I'll see you on the next one.

Sign Up