William Rosener: Also, Dr. Bill, welcome back. And this video you'll learn how to create a ticket sales calculator using Microsoft Excel and Visual Basic
William Rosener: Let's start by taking a look at which are not great. And then we'll walk through this step by step.
William Rosener: So we're going to have allow the user to enter in the number of tickets. For example, if I put two I can hit to calculate price.
William Rosener: And I can see if it's 123 tickets, the price is $50 per ticket. So that's going to be $100 if I was to come along and enter in the value of for
William Rosener: It to calculate price. It's going to be hundred and $80 and we're going to do this twice. Once you send the hip statement a second time using the case statement and we'll go ahead and press create a clear button.
William Rosener: But now that we know what what to do. Let's get started here. So the first step I'm going to do is come along and I'm going to press on the Start icon and I'm going to go to Microsoft Excel.
William Rosener: Again, it's going to open it up and let me go ahead and
William Rosener: maximize that. So the very first step we're going to do is we're going to type in ticket sales and your name. I'm going to see, I'm gonna have that information go from cells. A 131
William Rosener: Someone go ahead and go ahead and I'm attacking ticket sales and then going to hold the Alt key down and press Enter. That was all. Enter and go ahead and type your name.
William Rosener: Okay, now I want this information to span a on 31 some of the highlights the store sells. I'm gonna come up here and press the merge and center and then maybe I'll increase the font size a little bit here.
William Rosener: And then I'm going to hand position my cursor here and ever so slightly increase the with some of these columns here.
William Rosener: Okay, let's go ahead and add some color here. Somebody press on this little paint bucket fulfill color and a characteristic as shade of any color you want them. Then if you want to put a nice thick border around the outside there.
William Rosener: Okay, so we've got this much, then I'm going to put in number of tickets and the total price.
William Rosener: Someone come right here on the typed in number of tickets
William Rosener: And the total price.
William Rosener: And the same thing there with both of these. We're going to go ahead and Merchant Center have that information merge. Definitely. You'll see that as increase the font size here a little bit. We're going to want to increase Merchant Center that
William Rosener: You know, go ahead and line that to the ride and maybe even increase
William Rosener: It's just a little bit them. Okay. So same thing here. I'm going to increase the font size this case to 20
William Rosener: A merchant center line right
William Rosener: I like that virgin center. The Empress line right. Okay, so now we're going to enter some definitely this one here, we're going to change this to machine a machine to shade of blue
William Rosener: Possibly put a nice thick border around the outside and thought a price. We could go with blue or maybe in some other color.
William Rosener: And just the so that the user kind of clear says, Okay, I need to enter a value in here and here's where the total price will be calculated.
William Rosener: Day one, maybe I'm just going to go ahead and increase all this one size to 20
William Rosener: Okay, so now take a look here. I'm going to kind of match. So I'm going to put this in ru six number of tickets like it. See, that's going to span two columns and I'll put the price and the third column, see there
William Rosener: Okay, so here I'm gonna go ahead and type in a number of tickets
William Rosener: And then I'm going to type
William Rosener: The price. And again, I'm gonna go ahead and merge and center this information.
William Rosener: Okay. Same thing here. I'm going to type on one dash three
William Rosener: And if it changes that is no problem. I'm going to go ahead and do all of these. I'm just going to simply highlight this and I'm going to change the format to tax.
William Rosener: And I'm gonna have to re enter that one dash three
William Rosener: Four dash six
William Rosener: Seven to 10 tickets and then over 10
William Rosener: Okay, I'm going to go ahead and highlight these two Merchant Center. I like these Bruton center and repeat that.
William Rosener: Okay, then let me go ahead and highlight this
William Rosener: Underline it. And the price and underline it, let's go ahead and put some prices and put in 5540 and 35, let's say, go ahead and get the stuff formatted as currency, so I'm just gonna hide the cells and come right up here in format as currency.
William Rosener: Okay, so now we're pretty well got the interface ready, we need to. Next, create some I command buttons.
William Rosener: Some of the camera. Actually, I get the Developer tab is I've got mind as well. I'm gonna go ahead and turn it off your momentarily. And then I'll
William Rosener: Walk you through that steps on how to turn it on. So, in your case, most likely the Developer tab is non visible. So you're gonna want to come over here and a file.
William Rosener: You're going to choose options, you're going to choose customize ribbon and come over here and highlight developer and press OK, and I could say I've got my Developer tab visible.
William Rosener: Center, the Developer tab, under President this little down arrow under active X controls and we create a command button.
William Rosener: And I'll simply go ahead and drag it on. Now I'm not going to right click on this, let me go to properties, it will do a couple of things here. I'm gonna call this one, C and D.
William Rosener: Calculate diff. And then on the caption. We call this one calculate price lose parentheses type if and then well here. Let me increase this fun size a little bit here.
William Rosener: Okay, okay, all that good.
William Rosener: Okay, so I've got one command better not going to simply copy that, paste
William Rosener: And then paste. One more time.
William Rosener: I'm now going to go ahead and right click on this, go to properties. I'm going to call this one, C and D.
William Rosener: Calculate
William Rosener: Case.
William Rosener: And then on the caption.
William Rosener: Case falling down here on the call this one CMT for
William Rosener: Clear
William Rosener: And the caption.
William Rosener: Will be clear.
William Rosener: renaming it is optional but you're going to see when I double click here in a second, then it's a little bit more obvious. The name is actually going to match.
William Rosener: Or close like what I'm trying to do them. Okay. So we're ready to get going here. I'm going to double click on calculate price if
William Rosener: And sure enough, you can see now it's called C and D calculate if if I click on it, any code between this private seven in some will get executed.
William Rosener: And the very first thing we do is declared critical variables, one for
William Rosener: Tickets. And that's going to be a pipe integer. So it's going to hold whole numbers like 12345. I mean, I think I've told you before, if you ever get to the point where you have a new uniquely identified that I can just press the Tab key and finish that out.
William Rosener: And then I'm going to
William Rosener: declare a variable.
William Rosener: Price as currency and so it's one subtype. See you are. I can press the Tab key and complete that
William Rosener: OK, so now I'm going to take. I'm going to find the information for tickets, and as soon as I typed in cells. I'm going to see a row column.
William Rosener: And maybe we should come back here to this one here. So I'm going to be grabbing this from row two, column 3123 wrote two column 302
William Rosener: So you can see the room.
William Rosener: And the column.
William Rosener: And there we go. So I've now taken the information and placed into a variable called tickets, I can now use an if statement.
William Rosener: And I can take a look. If it's less than one.
William Rosener: Moment just gonna say price is equal to zero, something better than negative number, or zero price is 006
William Rosener: Tickets
William Rosener: Is greater than or equal to one.
William Rosener: And I used to put a space.
William Rosener: And
William Rosener: It gets as less than or equal to three.
William Rosener: And I believe if I was to hit the enter key. I'll do that. I think you're going to find it automatically kind of formats. This for me. So I've actually kind of cram it in it's going to go and put it out there for me.
William Rosener: I'm gonna take price is equal to the number of tickets times $50 in order to come along and modify that. But if I was to take a look if I've got 123 tickets, one, two, or three. I'm going to take the number of tickets times $50 and I'll be placed that in price or eventually
William Rosener: Okay and I'm just gonna do a little copy and paste in here.
William Rosener: Okay if tickets is between is between the number of tickets is between four
William Rosener: And six, the price is going to be $45
William Rosener: It's between seven and 10
William Rosener: The price is going to be $40 per ticket.
William Rosener: Else.
William Rosener: Is is equal to tickets times 35 I then in my statement.
William Rosener: Okay, so the first first line up here, this look this up here is just declaring some variable sudden some memory aside.
William Rosener: This line. It's going to take what's in sales, two, three, and placed in my variable tickets on they're going to take a look is tickets, less than one. Okay, the price is zero.
William Rosener: If it's between one and three greater than one less than three. I'm gonna take tickets times $50 etc. So right now, computed the total price. Now what I need to do is display it. And where do I want to display that I'm going to display that and row three, column three.
William Rosener: So I'm gonna go ahead and come right down here. Let's say cells three three is equal to price.
William Rosener: Okay. And I'll go ahead and close that. And by now exit the design mode I put that to one here to maybe I'm
William Rosener: Out. I'm going to increase that font size here in a second. So maybe I'll go ahead and do that. Next I'm gonna highlight these two cells.
William Rosener: And let me see how big I can get here maybe try 20 point
William Rosener: Okay, so if I put in two tickets. I can see it's $100 and if I was to put in form.
William Rosener: And calculate price. It's going to be 180 there. Okay, this is great. The one drawback to this is if I gave this to someone else and say they can use this great interface. The and they go, Well, I want to change the price. I'm going to change this to 16
William Rosener: Well, this isn't going to work anymore.
William Rosener: It's not reflected in these new prices. So what would be nice is if our code does not. It's not hard coded with these values. So rather than plugging in this was a 50
William Rosener: Rather than plugging in these values. I'm going to take the information. So I'm going to see it's 789 and 10 and then it's going to always be row 3123 so I'm going to take the information and seven three. If it's 123 and that should make sense or a second
William Rosener: Okay, so rather than multiply that by 50 I'm going to multiply it by the information and 7307 column three. This one is going to be cells.
William Rosener: Cells.
William Rosener: Let's go ahead and close that out. So now it's no longer hard coded
William Rosener: And I tell you what I typically. I'm going to put Option Explicit on here, just like we did some earlier assignments and then once you put your name up here.
William Rosener: We have you print out your source code, you know that you print it out years and not someone else's. OK, so now it's going to be the exact same scenario by
William Rosener: One.
William Rosener: It's going to be $50 by hit that three, it's going to be 150 but now watch. If I change this price here to 16
William Rosener: So I can get this nagging give this interface to someone that does not know programming at all. And they could come along and go okay 60 is 180
William Rosener: Or two is 128 so now rather than hard coding it I'm simply taking the values in these text boxes, when I do my multiplication for the number of tickets. So if I was to put in 10
William Rosener: And it to calculate price. I can see seven to 10 is 40 so I'm going to tell you what, let me just change this here, put it to 30 here momentarily.
William Rosener: Calculate the price and I can see it's reflected their stuff I got 10 tickets seven to 10 $30 each that would be 300 level for these kind of back here.
William Rosener: For someone gets a really good deal on these tickets here okay so 10 $40 ok now let's do the color. But here, then we'll come along and do the calculate price using the case. So I'm going to press on the Design Mode would double click on Claire and all we want to do is
William Rosener: erase any information that's going to be rude to column three and row three, column three.
William Rosener: Someone's going to come along and say cells.
William Rosener: Wrote column through
William Rosener: To the Austrian
William Rosener: And sells three common three is equal to the Gnostics that's one double quote
William Rosener: Unquote. But don't go into anything in there just to double quotes side by side. And we'll close out
William Rosener: OK, so now if I press on the clear button, sure enough, it is going to remove any information they're looking this last step here is the Catholic price using the case and what I'm gonna do is a double click right here. I'm going to grab all this code.
William Rosener: Copy it
William Rosener: I'm not going to turn around. Double click on the calculate price using the case.
William Rosener: And I'm going to paste it. Then I'm just going to take this information right here and delete it.
William Rosener: OK, so now we've got the number of tickets autumn new do is come along the type of us a slight case statement.
William Rosener: On tickets select case tickets. I'm not going to put Kate's 123
William Rosener: And the price.
William Rosener: Is equal to
William Rosener: It gets them into title, but maybe hard coded the first time, then we can always reverse it, just so that you're not really getting all everything
William Rosener: All confused here. Initially, so I'm going to take tickets times 50 so I'm gonna take 5045 40 and then if we want we can put variable syrup access this data rather than plugging in the 50s.
William Rosener: Okay, now I'm going to go ahead and highlight this information and will paste this a couple times.
William Rosener: Okay, I'm gonna go the next one would be four.
William Rosener: To six and price would be 47 to 10 and it's going to be 14
William Rosener: Case is greater than 10
William Rosener: And that's going to be
William Rosener: Here we're gonna put a case else
William Rosener: Rice equals zero. And then we're going to go ahead and in
William Rosener: The SELECT statement.
William Rosener: Okay. So if I've got a negative number, or zero, it's going to come on down here and it's going to execute this and put
William Rosener: Zero for price, but if I got one, two or three tickets or between one to three. I'm gonna take $50 times, there were tickets and a second price. So if I was to run this code now put in them to
William Rosener: It to calculate price.
William Rosener: It's going to display $100 there. And again, what would be nice IS LET'S GO AHEAD AND NOW modify this. So rather than hard coding it is put the information in these particular cells someone come along and come right up here.
William Rosener: And rather than putting in 15. I'm going to put in the information and sells
William Rosener: Three other
William Rosener: 737-383-9310 three
William Rosener: Okay, and we're ready to check this out.
William Rosener: Okay, so now
William Rosener: Again, using initially here I could put into at the calculate price and it's going to display 100. But now, if I come down here and change this to 60
William Rosener: Hit the calculate price. It's 120 so now i'm not requiring the whoever is using this interface. They don't need to know. Visual Basic they don't need to know how to open up the Developer tab and modify
William Rosener: The source code there with those prices because now everything is right here. So if I change this to
William Rosener: Do a number I can use the look at them. I put 25 and I'm going to put in 10
William Rosener: Hit the calculate price.
William Rosener: And sure enough, I've got $25 per ticket that 10 tickets. That's going to be $250 and there's our little clear button.
William Rosener: So hopefully you know this video you learned how to use Microsoft Excel Visual Basic for the applications, just like we did with Microsoft Word. They show basic for the applications there.
William Rosener: To create a little simple little ticket calculator. Yeah, and I think we're finished there so I catch the next video. Thanks.