Logic Tests: Select Case

Quick reference

• 00:00 In this module I wanna look at a little more
• 00:05 complex conditional logic here.
• 00:09 And we are going to start with the "if' statement just to show you a slightly
• 00:13 different cause for it.
• 00:14 And how it actually works here and
• 00:15 then we'll compare it to something I believe it actually better.
• 00:18 So what we have on the left here is we have a list of credit card types.
• 00:22 And each credit card has a discount rate, that's the rate that the merchant has
• 00:25 to pay to the credit card company in order to accept your credit card.
• 00:29 So what we're gonna do is, I've selected Visa over here.
• 00:33 And I'm just going to start stepping through this.
• 00:35 And what you'll notice is that right now the routine is gonna read
• 00:38 the Selection.Value.
• 00:39 So it's gonna read the value of the selected cell of A4.
• 00:42 And then it's gonna check and see if that equals Visa.
• 00:44 And if it is, it's gonna take the selected cell,
• 00:47 it's going to offset zero rows and two columns.
• 00:51 So it's gonna kick it over two columns and
• 00:53 drop the rate of 1.65% into the cell and there we go.
• 00:57 It does it for minute says all right and if, then we're gone.
• 01:02 If we go on we take a look at MasterCard, what's gonna happen in this pricker case
• 01:06 here is it's gonna read the Selection.Value from the worksheet and
• 01:09 check if it is Visa and it says no it wasn't.
• 01:12 So then it's going to once again read the Selection.Value from the worksheet.
• 01:17 So that's the second time, it says yes it's MasterCard, no problem way to go.
• 01:22 When we get to Amex, it's going to go and read from the worksheet,
• 01:25 you can imagine this all takes time it maybe nanoseconds.
• 01:28 But after you're starting to do thousands and thousands of rows it starts to add up.
• 01:33 Selection of value gets read three times before it's actually gets written.
• 01:37 And if that value isn't there at all let's say that we go on and
• 01:40 select like cell here.
• 01:42 At this point you'll notice that it reach one, two,
• 01:44 three times before it finally comes back and says that it's not a valid card type.
• 01:48 So this is gonna slow things down.
• 01:51 For this reason we have another kind of test.
• 01:53 And this one is called the Select Case test.
• 01:55 So if I go and I bring Select Case into view here
• 01:58 you'll notice that this guy looks a little bit different.
• 02:00 It does the same thing, so
• 02:02 if I go back and I can actually delete these values here.
• 02:05 If I select VISA, you'll notice this time when I go in though and
• 02:08 I start stepping into this.
• 02:09 The first thing it does it reads to Select Case, and it reads the Selection.Value, so
• 02:13 it reads it once.
• 02:16 Unlike the previous instance where we're working with an if statement.
• 02:20 And every single item had to have its own line to be tested inside
• 02:24 a select case we can actually test multiple things on one line.
• 02:29 So if they're like systems we can do them all together.
• 02:31 All we do is we separate these by a comma which is kind of cool.
• 02:36 The other thing for reference in here is that the case is is always something that
• 02:39 we use except for the case else line.
• 02:42 We don't have an is there.
• 02:43 But it's always case is and some kind of mathematical operator.
• 02:47 So this can be equals, it could greater than, it could be less than or
• 02:52 could even be not equal to which we'll use this guys here for.
• 02:57 In this case when we're comparing text we generally want it to be either not equal
• 03:01 to or we want it to be equal to.
• 03:02 So this case we're equal to Visa or MasterCard is it one of those two items?
• 03:07 Yes it is, and therefore we're gonna write a selection offset and set the value up.
• 03:11 Now here's where thing get little bit more interesting though when we go back to
• 03:16 MasterCard let's go and grab guy here.
• 03:19 So we'll check the selection of value at MasterCard, yes it is.
• 03:22 Three lines in we are done, okay?
• 03:25 What about Amex?
• 03:26 If we go on and we take a look at this guy it reads the selection of value once,
• 03:31 it checks the Visa MasterCard, no.
• 03:33 It goes to Amex,
• 03:34 doesn't read the selection of value again and says yeah it's this one here.
• 03:37 Much shorter code block in order to get here.
• 03:40 And if we're into something completely different,
• 03:42 once again it only reads the value from once it says is not this, is not this,
• 03:46 it must be an else will give us that card type.
• 03:50 So this allows us to get into much shorter blocks of code.
• 03:54 If you're really trying to actually streamline your code because you don't
• 03:57 want to actually be dealing with too many branches.
• 03:59 I mean you could have 90 different caseloads t's gotta go through
• 04:03 all of them.
• 04:04 You could come up with a way to actually say you know what?
• 04:06 Maybe I'd like to actually like to look at the top of this particularly branch.
• 04:09 I'm gonna put 15 things in here and then I'll nest a secondary,
• 04:14 logic piece inside my other logic here.
• 04:17 So let me just try and get all this on screen here for this routine.
• 04:20 This is a nested routine, so here's what's gonna happen.
• 04:22 And we can use either an if statement inside select case.
• 04:25 Or we can use another select case and
• 04:26 we can nest these many levels deep if we need to.
• 04:29 But in this case what we're gonna recognize here is we're gonna check also
• 04:32 the premium card rate.
• 04:33 Is it a premium card, yes or no?
• 04:35 And that would mean do you have the correct aero miles or
• 04:37 anything like that on your visa.
• 04:39 Because if you do the company has to pay a higher rate in order to take that car
• 04:42 because a credit company used to recruit those customer somewhere.
• 04:45 It's the vendor that pays those bills.
• 04:48 So let's go and check this out.
• 04:50 We're gonna go and we're gonna step in and it's gonna say.
• 04:52 Let's do our select case.
• 04:53 We'll read from the selection out value.
• 04:55 We'll read that once and that will work for the Visa MasterCard, it'll work for
• 04:58 AMEX and it'll work for the else here.
• 05:01 So it says yes, it's a Visa or MasterCard.
• 05:03 Now we're gonna read the case of the Selection.Offset.
• 05:05 So we're gonna go and take a look at the yes.
• 05:08 Is it yes?
• 05:09 In that case, we'll go and we'll write this rate in.
• 05:12 And then we can step out of the routine.
• 05:15 In this particular case here it would be we'll read selection dot value once.
• 05:20 We'll test it.
• 05:21 We'll read the offset once, we'll test it.
• 05:22 Is it yes?
• 05:23 No? It's a no.
• 05:24 So we'll go and we'll write a different discount rate in place.
• 05:29 If it's an Amex, in this case Amex only has when discount rate.
• 05:33 So is it a user MasterCard?
• 05:35 Nope.
• 05:36 So we never read the selection at Offset because we don't need to here.
• 05:40 We will use it in this particular case here for the selection Offset,
• 05:43 two we're gonna write to this particular cell over here, okay.
• 05:46 So it allows us the very easy ways to go actually nest multiple cases in here.
• 05:51 So this is a nested case that's actually going inside.
• 05:53 We could use an if block in here if we wanted to.
• 05:55 We could even nest multiple ifs if we wanted to as well.
• 05:58 But what I generally find is the select case is a little bit cleaner for things
• 06:01 where the logic starts to become more complicated when it's got more than two or
• 06:03 three options to work with.

Lesson notes are only available for subscribers.