William Rosener: This is Dr. Bill, welcome back. And this video you'll learn that create a little calculator skate away order form.
William Rosener: using Microsoft Excel and Visual Basic. Let's start by taking a look at what you learn how to create it and we'll walk. I'll walk you through it. So we're gonna allow the user to enter in a name.
William Rosener: They can enter the number of yellow skates they wish to purchase number of blue skates they wish to purchase that it to calculate price and two things are going to do happen.
William Rosener: I can see it for more than one to 10 skates there 10 hundred dollars each. That'd be 300 and we do a string concatenation taking the person's name.
William Rosener: Along with total order was and the price or the mouth and they make things a little more interested in what go ahead take 10% off all orders over $1,000
William Rosener: And we'll do this twice. Once using this statement when she's done a case statement in the moment, create a clear button.
William Rosener: Well, in the same areas you found a link to this video, you should also find the interface save a little time to create all the command buttons in type all this text there.
William Rosener: So let's go ahead and get started here. I'm gonna double click on I'm in the interface here. Double click on to calculate price using the if statement in the very first step. I'm gonna do is I create some variables here, one for the customer name.
William Rosener: And this is going to be a type string.
William Rosener: Going to create some of the variables for the yellow skates that they wish to purchase.
William Rosener: If I wanted to, I could put this on its own line and the blue skates.
William Rosener: Excel, but since both of these are the same type of home integers. I can go ahead and put yellow skates comma blue skates. And it was going to create a variable for total skates.
William Rosener: As integer
William Rosener: Israel and also wanting to create one for the price and it's going to be a current same enough budget for once you've been in this case subtype. See you are. I can press the techie and fill that out.
William Rosener: So, so far we've done is declared some variables. So it's a memory side. And now we're going to go ahead and start filling in some values here. So the very first thing I'm gonna do is they customer name.
William Rosener: And I'm gonna find out, well where's that data at so I'm gonna come along here and take customer name. It is in row 2123 column through
William Rosener: So look, put in a row two, column three is the customer name, and I'm going to grab the yellow skates.
William Rosener: And where is that, and that's going to be in room three columns read
William Rosener: The blue skates.
William Rosener: And that's in sells for three
William Rosener: And now that I've got the yellow skates in the blue skates. We can do a little addition here and I must say total skates.
William Rosener: Equal to my yellow skates.
William Rosener: Let's my blue skates.
William Rosener: And I would always recommend, but an Option Explicit turntable. Let me make a mistake here and I'll show you if I was to maybe I just momentarily called this yellow skate. I left off, plural. And if I was to run this down, it's not going to complain.
William Rosener: I run it to calculate price is like, okay, working on, it's not going to work, though, because what's going to happen when I get to
William Rosener: actually run this is yellow skates as a value of zero. It doesn't know what yellow skates is so it gives it a value of zero. But if I was to come up in here and type in auction.
William Rosener: Explicit now if I was to run this code.
William Rosener: It's going to, there's a variable not defined. It's like yellow escape, escape up. I forgot the S. So I'm going to put an S here on it and I'm gonna go ahead and close the debugger.
William Rosener: And now if I was to run that I can see it's no longer complaint in there, even though it's not we're not finished yet.
William Rosener: Okay, so come along. Double right here. So now I've read in some values I now know how many skates. The user wishes to purchase a week now use that we're start off with the if statement here.
William Rosener: And then we'll take a look at the total
William Rosener: skates. That's a greater than or equal to one.
William Rosener: And
William Rosener: Total skates.
William Rosener: Less than
110
William Rosener: Price is 100
William Rosener: Prices equal to total skates times 100. Okay, so just take a look here on if they order it forward in between one and 10 skates. The price is $100 per escape.
William Rosener: Okay, so if total skates is greater than one less than 10 less than or equal to 10 than the total skates at the price is the total skates times 100. And once we've got this. I'm going to do the copying and pasting here.
William Rosener: Okay, this will be Intel's one word total skates is between 11
William Rosener: And 21
William Rosener: Then it's going to be $95
William Rosener: For escape.
William Rosener: It's between 21
William Rosener: And
William Rosener: 2030 and it's going to be
William Rosener: Here and anything else. I'm just gonna put it needs to be an Elsa.
William Rosener: Else.
William Rosener: In that is 85 no need to make sure I end if statement to him.
William Rosener: Okay, some if also make sure that so one word. And if I was to put a try and put it as to, you can see BB is going to give you a little feedback here and read, put it in Red said, hey, there's a mistake here. So the LLC.
William Rosener: Is one word, but the end it is two words. So if the skates, or between one and 10, the price is 111 to 2025 21 through 39 eight and anything about that. It's going to be a five.
William Rosener: At this point we're ready to display the pricing the walk. I have to come back and modify this a little bit because I need to take off 10% off all orders. Okay, well where's this going, other places in row five column three.
William Rosener: So,
William Rosener: I've come on three is equal to the price. And now we can actually run it and get a value display there.
William Rosener: So if I come along here. I could put in my name, I could put in one yellow escapes when blue scale that the Catholic price. And sure enough,
William Rosener: It's taken us data and display in the total price of one to 10 skates there $100 each that would be $20. Okay, next step we need to do is that display the string concatenation. And then also take 10% off all orders over 1000 and maybe we can go ahead and do that first year.
William Rosener: Okay, so I'm going to come along and double click on my calculate price if you look, I'm right on down here and I'm going to simply say if the price is greater than 1000 then I will say price is equal to price minus
William Rosener: 10%
William Rosener: So what I'm gonna do is I'm gonna say the current price minus 10% in place that back and the variable price, then I'm gonna go ahead and display that okay so we could have run this here.
William Rosener: And let me go ahead and put in 11
William Rosener: Put 11 on this one. And I'll put zero
William Rosener: For the blue skates at the Catholic price if and sure enough, that's 148.50 and how did I get that I took 11 times 95
William Rosener: Of the 1045 and then almost subtract 10% which would be subtracted $104 and five cents. So when I do that subtraction. I'm gonna get 940 and 50 cents there.
William Rosener: So that's working correctly, they'll come back to a little bit smaller number here I put one and one. And we're back to 200 now ready to do our little string concatenation.
William Rosener: come right on down here, where am I going to play set. I'm going to place that in six one right here, the very beginning, six, one,
William Rosener: I'm going to put the customers name.
William Rosener: With
William Rosener: That said, I go rose nurse total order was
William Rosener: And price, but just make sure you get a
William Rosener: 10% here string concatenation. So I'm going to take information in the variable concatenate that is the percent. This is a static string. It's always going to say apostrophe s total order was and then the price.
William Rosener: Okay, so if I run this. Now it's going to say go roasters total order was 200 and I think you're I'd like to get this the dollar sign there somebody come along and
William Rosener: Come back here if you want to, you could put this on the same line. I want to put an underscore here in the space. I'm gonna say continue on the next slide. And I'm going to do is on the same format.
William Rosener: Price.
William Rosener: As currency.
William Rosener: So I'm gonna take the prize for minute is currency and display it.
William Rosener: Okay, now we're there. Okay. So at this point, we got the clear button and to calculate price. Use the case we go ahead and get that clear button. So I'm going to do 12345 fields and I'm gonna start off with two
William Rosener: And three row two, column three.
William Rosener: So the cells.
William Rosener: One.
William Rosener: And why six one because this one started in row six column one. Okay, so hopefully at this point when I hit the clear button. Everything is erased. OK. Now we're just going to come along here and ever want to fly. They create one program using the key statement.
William Rosener: So I'm going to go ahead and save time here, I'm just going to grab all the code, you can see the calculate if grab all this code.
William Rosener: Copy it
William Rosener: I'm having double click on the calculate price case statement. I can see here is the code calculate price case statement that's going to get executed. I'm just going to do a little pace term. Okay, at this point, I'm just going to delete this if statement.
William Rosener: You know, replace it with a select list.
William Rosener: Okay and case one to 10
William Rosener: Prices equal to the total
William Rosener: skates times 100 and I probably should maybe on this one. We will go ahead and rather than hard coding it with these values for reference, the data on the spreadsheet itself.
William Rosener: Happy. There's two lines.
William Rosener: And 21 to 30
William Rosener: Than anything over 30
William Rosener: Is a
William Rosener: Case else
William Rosener: Is going to put price equals zero. No. No need to
William Rosener: SELECT statement. Okay, so if it's one to 10. I'm going to take the hundred dollars times the total number skates. Put that in price. But if I get down to the point where it's like a negative number, or even zero. I'm just going to put zero in price there.
William Rosener: Okay, ready to run this year. I'm going to go ahead and close this.
William Rosener: And put a name in here.
William Rosener: I'm gonna put one and one. It's calculate price using the case statement and turn up. It's going to put the 200 and the 230. Okay, so what would be nice and then publish it up with your statement is
William Rosener: So right now, if I gave this to someone to us. And if they want to modify these prices they have to get in and modify the VB code. So that's going to make that change here. So this is going to be in nine for re somebody double click on the case statement.
William Rosener: So rather than put in 100. I'm going to put the cells nine three
William Rosener: And then this one's going to be
William Rosener: Cells.
William Rosener: And cells.
William Rosener: 12 three. Okay, so now if I come along here and modified so let's say the new price is $150
William Rosener: And when I hit the calculate of all i got it on the calculate price for the case right now.
William Rosener: If I run it. I can see. Sure enough, it's reflecting the new price of order to skates hundred $50 each. And here's the new price and let's just
William Rosener: Maybe I could put these said hundred some odd and 40 under 30 I'll put this one in 100
William Rosener: But actually, a couple things going to happen here is it's now going to put in 30
William Rosener: And zero
William Rosener: At the capital A price, it's going to
William Rosener: Take 30 times 100 but then it's going to subtract 10% there. And I think that's right. I'm gonna go ahead and check that here in a second, but hopefully this video you learned how to create some order form Microsoft Excel and Visual Basic