Excel VBA and Macros programming for Absolute Beginners | Muhammad Ali Yasin | Skillshare

Excel VBA and Macros programming for Absolute Beginners

Muhammad Ali Yasin

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
30 Lessons (1h 50m)
    • 1. Introduction to course and Me

      0:55
    • 2. What is Excel VBA ?

      4:27
    • 3. VBA Macro Recorder

      3:06
    • 4. Absolute vs Relative Reference

      7:22
    • 5. How to protect your code ?

      2:42
    • 6. Getting to know VBA environment

      5:46
    • 7. Excel VBA Variables

      4:17
    • 8. Learning Range,Cells,Active cell etc.

      8:30
    • 9. Introduction to VBA Sheets and Workbooks

      4:48
    • 10. Introduction To Functions

      5:20
    • 11. IF THEN ELSE Statement

      3:05
    • 12. SELECT CASE Statement

      4:23
    • 13. Do While and Do Until Loop Statement

      3:02
    • 14. VBA FOR Loop statement

      2:28
    • 15. Message Box detailed

      3:42
    • 16. Input Box detailed

      1:34
    • 17. Getting Started with Forms

      2:50
    • 18. coding close button and adding Text Boxes

      1:13
    • 19. How to access last row of worksheet in Excel

      2:35
    • 20. Adding Nationality and Date of Birth to project

      3:22
    • 21. Adding Combo Box

      3:51
    • 22. Adding Gender and Contact Number

      4:11
    • 23. Recreating project using Active X Controls

      3:05
    • 24. VBA AutoOpen

      1:36
    • 25. Object Variables and With statement_ Learn to stop repeating

      3:48
    • 26. Opening Notepad With VBA

      6:45
    • 27. Make VBA Speak for us

      2:38
    • 28. How to paste data to new workbook and save it

      3:09
    • 29. Creating Duplicates exists program

      2:51
    • 30. How to delay the code execution

      2:17

About This Class

Did you know that repetition is a curse, if you don't stop then you'll be wasting thousands of hours of your life. As an Excel user, you know that many times you create same sheet, with same formatting and same formulas, again and again. So, you're wasting thousands of hours, which you can spend with your family, or doing other important stuff.

Luckily, there is a tool or a language using which you can save those thousand hours of your life by creating a simple program which does the work for you. The program will create the sheet for you, it'll format it for you and it'll insert the formulas you. Not just that It'll protect your data in different ways.

Excel VBA, the programming language we're going to learn in this course which'll help you automate your daily tasks more smarter and more easier. You'll be able to tackle different and complicated situations easier.

So, don't waste anymore hour or second of your life and take this course immediately, uncover the tools which'll make your life easier.

Transcripts

1. Introduction to course and Me: Hello and s American guys and girls first in first. What is a serum article? It basically means peace be upon you. So I don't think it's a bad thing. And don't get offended over it, OK? No, a little bit about me. I'm Mom. It early on are been teaching online on face to face for years and have correct knowledge or programming languages such as HTML, CSS, JavaScript, BB A, C C sharp and stuff like that and other languages such as Arabic and weirdo. A little bit about the course. So basically discourses for absolute beginners and to teach you the beginner, the things that you need and not stuff in your mind with useless thing. So not a single video is useless in this. Cursed. Lastly, this course isn't a low pace cars, so you won't have any time to get for guys also in the next trail, by way 2. What is Excel VBA ?: Hello, Ennis. I want them guys. Welcome to introduction toe rags. Lt. B A. What is with the A three Being is an acronym for regional Basic for application. It is used for automating tasks in exile. Berating macros on Let us get started. So let us right off course MCA. How do you write on macro? First go to a black sheet. So now you have to right Click in here on the ribbon on Go to customize ribbon Click And you see something called Developer to turn it on. We have to check this and click. OK, you can do so from here. Or you can go to file and options on from there. Go to customize ribbon on Go to developer turn it on or chicken and then sleep. Now you have developer tap. This is the area which we used for working with the B A. So first we need to go to redraw Basic visual basic is what it is. The application on which we right over the So the first thing you need to do is right. Click in here on go to insert and click a module or you can go toe insert from the top Manu Andan module. Now you have this first king First you have to say something means I'm creating a program on treating a macro that is named hello world on. Remember that you are not allowed spaces. You are not allowed to have number at the start and a lot of other things you can have special directors on. And if you are like falling along the laws, then you're OK with that and you can name it. Once you press enter, you will get Hank's up. Now. In here, you have to say message talks means I want a message boards to appear saying Hello world on , then just come down. It will capitalized itself on. We're good to go now. You have to press the play button or up five to rent this court. I press a five or play button and I get my message saying Hello, world. What if I want to get any other message while I can do so by saying message box then saying I know the world on that is different. I don't know why, but I love to use hello world with another world. This sounds really nice. to me. Hello, world then another world. Now let to see this I have Hello and I want the world to be down. How do I get the vault to the second line? You can do so number one by saying by separating these two on in between your Andi and and in between. And you have to say BBC are up. Now you're saying hello on call caffeinated vit. A new line means after hello at a new line on after new line add rolled you can use plus in here but in some cases you conscious plus in some consists you can so let us see you see Hello world But it is recommended to use on sign rather than the plus sign because plus scientists on arithmetic operator and it is not recommended mostly to use this because it's gonna create a lot of problem for you in the future. Well, if you want to save your enter vote book and it has mackerel, it has to be saved differently. How do you say that? Let us see that you go to save your go to's browse to place you where you want to say out Goto my deck. Stop on out! Call this my first tutorial. Now from the city as tight as you can see, I'll go to Axl. Macro enables Fert book The macro means vb a sore exile. VB enabled workbook. Now save it. And that is Oh, okay, guys. Thank you. I hope you like this video and I will be waiting you in the next. True, but by 3. VBA Macro Recorder: Hello In a silent room, guys, welcome to destroy. Today I'm gonna be teaching about Makary Korda. What is the macro recorder? You see, Something caused the court macro in here. Let us get story. First of all, let me tell you what is a macro a macro or a VP is something which helps you not repeat your work. It's gonna automate your work. It's gonna make it automatic. You don't need to repeat yourself again and again. Let let us consider you create a sheet every day. So we have a table which we created on. I have to create it. Every single dick. Or maybe I had. I need to create it every other. And it's like, Don't you don't want to do it anyway. Why would I do the same stuff again? A again a. My immediate. Once I have done it, I confuse it again. I should be able tow automate that thing. Well, maybe helps you to do so. If you record a mega, let us record it in seat two. Let's go to developer time and let us record macro and let us create the same thing. But a small in small size Now let us top or macro recording. You can stop it from here. Or you can stop it from here. It's up to you once you have stopped it. Now, every time you need to create a sheet like this, what you do, I basically go to, um, accurate and Renda Macro one. Go to macros and random macro fund. Or basically, I can press also, if it on then bring this macro. And if I goto macros and or toe Mecca one. What I see is this thing it went to so this. So Andi gave it first name. And then on this last second, sir, it gave it last name, then class, then whatever you see. So it's selected the B one selected the few. Inspector, the D once elected that, uh, everything from a one to it. The d train teachers. So a one two d 22 I have given them Ah, this border. So everything you do is is going to get reported. That's all. That is a lot about a macro recorder. Once you get that, I'll see you in the next room. Please make sure to practice because we are gonna have a project on this. Okay? Also in the next room by 4. Absolute vs Relative Reference: Hello, Anna Sonic. Um, guys, welcome to this tutorial and indestructible. We're gonna be learning about the relative references. What is the difference between an absolute reference and relative reference to? First of all, I have I'm on human, right? I go, I'm gonna recorder macro. Still to Mecca one, or I'm gonna call it absolute reference. I'm gonna click. OK, now I go to see one on I type my name. And now I am one See to I'm gonna stop recording and go to Macros. And this is absolute reference. If I added this, if I look what is happening? First it went to see one, and it selected that. So if I don't see, it was on, even they didn't know, do anything like it was on here. So nothing happened. So it went to see one. Okay. So far, Step then. When I type something, Then it went to see toe. So these are the steps that happened. It went first, elected to see one, then answered Ali and then went toe Sito. Okay, so if I want to rent to see this same Brecher, I can run it on new sheet on. I can just go in here and run and advocacy. The same step happen. I didn't need to repeat anything. It's a good thing. No. If I want to repeat the same thing, like in here, I want I want it toe being here. Then go to C four, then go. One step down. How can I do that? Well, for that, you have to use the relative preference. Let's actually record this. We're gonna select a relative reference and start recording on is gonna be relative reference relative to Right. Okay, now, I'm gonna go to see, um four and type again. Similarly elite, and then goto C five. Now I'm gonna stop recording. Andi, stop the relative reference. Now, what happened is if I goto this and good relative reference for actually Sorry. Let me go in the units relative reference and go at it. Let's see what is happening. Basically, what is happening is it says I, um, on whatever self, I'm inactives. Ellman's whatever self is selected. Go to columns towards right. So offset two columns is go to columns to where it's right. Well, if it's minus two, so it's going to say go to column strolls left but it is saying go to column stored strike . So if it's in here, is gonna go frugal and swords. Right? OK, good. Then after that is going to say on that column type, right, Ali. So in this scholar is done, right, honey? Okay, then it's gonna go one brought down. Okay, so on the active cell, no wonder Down inactives up. Go one road out that Just write this in here. I'm gonna goto a record, Matt Rose on Run the relative reference and run it. And as you can see, this is similar to what we have learned. So let me to repeat the same thing again and again. It's gonna if this is absolute sorry island for absolutely go again for relative one And as a conceit relative, one works really good. Why is it useful? Notice? Support? Uh, have particular data, like from John, cause I don't I don't want to type any day tougher now sound and just, um two months. Okay, so these are my my particular data. I want it every time I get data or a particular data is some very, for example. I have to state it in here on this data in here, so I have to date. That's right. I want the relative reference to do Is put one in here and one in here. Obviously, I don't know how much I will have. What I can do is I can say the selection which is selected so I can go select something on . I can record a macro. Meco. Well, that's a test relative. Put that front. I'm not sure about this felon. Okay? I'm gonna say it reduced relative reference, and I'm gonna just Curtis and control left on gun control up London Pacers. Okay. No, I'm gonna, uh, stop this quarter and stop this recorded story. You stop the relative reference and stop the recorder. Now, if Actuals Days Onda run the test of relative macro what it does Sorry, there was a problem. Actually. Let me repeat this process again. Let me just chris this in here for the sake off. Yeah, easiness. So we have it again here. And if we go and edit this what happened? We have to solve this problem. The first test relative, it goes and cuts the whatever thing we have and it cuts the selection, and it goes toe left and pissed it. No problem. And exit up. Okay, then pasted. Actually, after going toe Edsel up. We need it. Took come a step towards right. Okay, So what happens is it goes, it's it co cuts this okay and then goes towards the left. On goes up, as you can see towards the Latin Means is gonna keep testing on this section. What we want it is deep. Based it on the last unpopulated column. So what we can do is we can ask it to say selection, not a cent or active soldiers. Offset is better. Just so don't the same thing in here investing in here. It's better. So let's copy this on. Let's see what happens. So it's going at raw when raw towards down. We don't need one road Words down. Actually, we need one concerts, right? Okay. And then we slept that thing. No problem. Right on Now, only it said this. Okay yourself, this or now what we do is beast like this again and we run the test metre and as agency pissed is there on we run the past. My test may occur again and again. Pistes if there is really, really, really useful. The more complicated you make it, the more useful it be and the more awesome it will be. Now we have looked just at a simple program that to suppose we have thousands off complicated things that we want to take and put stuff in there and whatever we want some automation. We can really do this, and this really helps us do that. Did I say something stupid? Whatever you guys, that was all and also Internet stripped by where. 5. How to protect your code ?: Hello and demonic. Welcome, guys. Today I'm going to show you how you can protect your court from other uses. So how can you do that? You go to old after 11. This is your card, right? Go to tools. Goto BB A project Properties in here. You name your project? Checking frequency above five. Like your points years or five. Okay, that is my project. I can't have a number at the story. I do know you are not allowed to do so. I can't have a special sound like opening bracket, opening bracket and closing bracket Apprentices. No, you can't have that. You can't have space. You can't be like this. Checking. Okay, So your name should not contain any special characters. It should not contain a number at the beginning. It should not have spaces, that's what. Okay, if I try saving this, which obviously looks good. We're gonna have an error again. Let us see. What? Wow. Why? Because we have point. We have period in here. We need to take that period on. Let's see now. It is good. Now it is the You have a project and it has a name. Let's go back or two tools. Goto checking frequency above 005 properties. Now you can have description like, um Freaking and we know something about on you. Go to production Love project for viewing. It's gonna look your project. You need a password. Your 1234 Come back again. Here. 1234 on. Click on. OK, close then let us close this workbook Close section. Okay, now that has come back on. Let us open the frequency again. This is all fake currency. If we go old half a lemon, we see this section in here. If I try opening this, it's gonna ask me for password. And it just really important for you to remember this part for because it's really hard to get the password back. You know, if you're programming in, like 2013 2016 or 2010 it's gonna be hard for you to retrieve the password. Obviously, I didn't find any software until now, so don't do that. Put a decent password back it up on. It is really important for you to remember it. 01234 now. OK, access your project. Early eyes. I'll tell you in the next. Thrilled by by 6. Getting to know VBA environment: hello and a cinematic today we are going to be looking at our VP a environment. We be a program. So let us get started. We're gonna say old every lamp on this is our program when it doesn't have anything. So fasting first. We are Project Explorer. Basically, it tells you the first is on which you can call. You can call in sheet one. You can accord in this world boat or right clicking here and insert. I'm user form module class modules and you can add your court. It is pretty simple. Now we have Secretary card properties Window. What is properties Window? Basically appreciate Has a property. Every book book has a property. Every matter has a property, even if it is a single. For example, this I had a matter on this module or matter has a property card name. I can name changes toe living birds. So it changes its name so tumultuous can have a name. Why do you want a name? Because you can't say much in one word. You tore your margin. Three. Come on. You have to have a specific name. And by the way, what is Module module is basically a piece off program a piece off instructions using which you can do something on that thing can be rendered through a click. Or you can go to developer on macros and you see your macro in here. So let me give you an example. So basically, I create a simple program like this. I just record a macro. Name it. Clear everything. I could take a shortcut key. What is a short cut key? Basically, when you press the shortcut key, this program is gonna good triggered. It's gonna ruin. I say control ship A on. I want it. In this book book, we give it a description. It's going to clear re thing in active self actives elements. The currents of the selected, sir. So once we're done that we've got a horn. Go to clear and clear. All good. We come back, we stop recording Necker. Know what happens if I type something in here? Number one. I can select this and go to Macros and renders its gonna triggered the court. Good. Let's do it again. Number two I can go to insert go to a form control. I can click in here. I can assign it to this. So whenever I press this button, this is gonna get critic heard, for example, I'm on nine act. If I click on this, nothing happens because it is already empty. But if I select this and press this button is gonna clear it, just get it back again. Now, the last thing you can attach it to, the picture we've been attaching did a ship. So I have a shape like circular shape on our right click on it and say, assigned macro and I assigned macro to it. And when I click on this, select this click on this, it's gonna disappear. So basically, ah, matter is a piece of program which can be attached to any object. Or you can access that macro using a shortcut key. For example, in our case controls shift A deletes it, or you can go to Makro it and run it. What is a macro? A piece of program? A separate piece of program which can friendship one rush Ito worship three or even in other sheets. Okay, so if I have this sheet, I got to file new and I create a new shit, A new book sorry. And this book is called Book Tour. Remember that we have created our macro in book one Right now. We go in here, huh? If I go to developer Makris, I see Book one macro, that is called clear. Everything for Rendez. I can run it in every single active What book? Okay, so this is what back macro basically is. Then we have sheet one. So basically sheet one means you want a separate court are restricted court Onley in sheet one. It won't work. And she too. It won't work in any other book. OK? Similarly, would it see to it? See tree this workbook. It's basically a cord which is going to walk in this book book. Only you can't make it to work in other work off live a tidbit matter. So lasting last I'm gonna show you how you can add these thing in here common and uncommon . For example, if I select all these on just say uncommon, it's gonna un comment them all. And if I select all these and comment, it's gonna comment them all. Basically, it's gonna save you a ton of time. Okay, So what is a comment? A comment is a piece off text piece of string. Which program doesn't look at it? Which compiler doesn't look at it? It's gonna think of it as the user has put the programmer passport to understand this court , for example, In here we have this clear everything and we have its description is going to clear everything in the Mac in the actives are basically it's a comment, right clicking here and go toe Add it on then in here. Right click and go to customize on Goto added again, of course. Go down. You see this? Take it and bring it up. Take the other one and drink it here. That's all. Okay, guys, I'll see you in the next drill. Thank you very much for watching and bye bye. 7. Excel VBA Variables: Hello, Anderson. American guys, welcome to this tutorial. Today we are going to be learning about very boats. What are variables? Well, you may have heard in your class. That ax equals 25 means X is assigned a value 25 or X contains the value 25. The very well is that because it contains some value? A variable doesn't mean that it is X or Y or Z. It can be a combination off letters. Okay, so first name is a variable and it contains the value of the So whenever I access first name, I actually access Polly. So basically it can't Is the name Ali? Well, it is hard to spend like that. Like me show you an example. So we have here Dim X as interest earned means acts is a very well and it will contain interference. Into your value is what we say. Integer is gumbo on. We have dim named as strength, and this is string is text. So X will have a value which will be a number and name will have a value of which will be up text. So, as you can see, X contains a value which is 25 which is a number and name. Contains belly, which is Ali, which is, uh, text. So whenever I print name, it will print it meant won't be print this thing. It will print what it contains, right when I want to print. Exe our se X, but it will print the value of X. Okay, has your fancy Ali. And if I print, that's what will happen. It will print what it will print 25. Isn't that great? Well, let us look at another example, and I'll be creating it in front of So now everything is clear that that started our example. I'm gonna say Okay, now our program is ready. And it says I have a verbal called egg and it felt content interoperable called first name , and it will contain text. And I were both last name and it will contain a strength. So the wave you're creating variables is Carver. But declaration have a verbal age, and it will contain. But our I input I'm a first name. It will contain whatever I've input. I have a last name. It will contain whatever in fort have a message. Both which will say first name and it will print first them which I input. And it will create a new line. And last name on it will put the last name which I input. And it's gonna say age and it will print age, that's all. Let us see edges like my interest for training. First name would be Ali lost members seen. So my first name is lost. Time possessing and it is 20. Well, this is car concatenation means I am merging these truths Cells okay, are emerging this text because it will be printed as it is first name will be printed. As it is, it's just a first name. Then we'll give it space and it's going to say After that, I will say, first name on this full first name will content ELISA. It's gonna say first name Ali that is going to create a new line because VPC are left means a new line that it will say last name on it will print my last name. Then it will say BBC are left and it's gonna say age and it was in space off this. It will print the value of fish that would be 20 I hope you understand, because this is kind of a little bit complex because we're getting into conflicts. Programming on this is one of the complex things. So if you get this, I'll tell you in the next year, if you don't get this, please make sure to practice it on. Inshallah, you'll get it. That's all CIA. 8. Learning Range,Cells,Active cell etc.: Hello and s American guys, Welcome to this tutorial. Today we are going to be learning about rained active cell and sells. So let us get started. What is a range? If you want to select a specific cells using we be a you use range, you use self. And if that's how is this active cell, for example, this If I want to do with something with this active self, I call it act yourself and I do something with that. Enough with the Turi. Let's go toe old lemon and we have a procedure or a module prepared. Let us suppose I want to select a one what I'm gonna do I'm gonna say range a one in double court dot Select So what will happen? A one will get selected. Let us suppose I want to select a couple of cells which are like here, Here, here, our hill I don't care. Well, that was nice. So it won bae 15 see 10 or a 20 These ourselves Jewish I want to select. I'm going to just run this and a one Seaton B 15 and 20 half being selected. What? Let us suppose that I have a radar Cells like this is the range of salts which is equals ran between and content school from 200 to 1000 rebels on I named this range as my books. That's it. Ok, so this is my books. This renders called my books And if I want to change its background color, what will I do? I will basically go in here accompanists their range. This time it's called my box and dot interior door for a look equals we be Let's say green what will happen when you run this the cells a one a B 15 seat and it and will be selected . And the range, which is called my boats will have interior color baby green. But the strange thing is, you don't need to select this when this is selected, but this will change. You don't need to sex something to change its value. We will see this right now. If I press f it and then again it will go through the program step by. Step on again. It's gonna select all the cells which are in here and again is going to change its background color, background color off my parks range, my boss. So the range my books has green color. Okay, if you have seen this now what? Now let us see the shortcut for range while writing raised like this big is always a hassle . What you to visit? What you two? You say opening square bracket closing square bracket. And in between you write your values. Like how much is like be 15 on? I'm gonna say, daughter select and you can select it. Bae 15 has been selected. Well, there are some advantages and disadvantages off using this thing. When you use the range using this method, you don't have a teller since list. What is that? Ellison's list. This is controls piss. You get this one so you if you range, then you can access your read like a one. Then don't value. As you can see, this list gives us a lot of things which we can't even remember. So it is a good thing, but with that kind of French, you don't use it with this. You don't get it. Stenson's list. I'll give you approve. If I just say well equals Welcome. Now if I come in here and try accessing the tell assist dot It doesn't give me 10 cents list after daughter. It doesn't give me talismans list. And that is man. Well, it has some disadvantages, but it is really useful. So let us learn about this a little bit more. Well, let us suppose I want to select a 1 15 20 So that is the same method. You can select it and you confront it, and it's gonna be when it's gonna go first. It's gonna second the 15. Then it's gonna select a one a 15 and a 20 that is really good. You can still select your rangers and this you can say my boss on get to see use lecture, my box, the range or the name named range, which is called my boards. Now I'm going to something called cells. What itself? Well, using cells you can access your stretch it with roles and columns. You can select your roles and structure columns, and that's all have to suppose I want to select Throw one and column one dot select. And if I run this Roman and column one is selected. If I say cells right. Two column fifth column Tree Let us suppose on a thought, but also tell sins List doesn't appear so that is again a big disadvantage. But these are useful. And you say, like, interior door color in Coldstream Creek. Really red. Now, if I were in this the sea that would meet third column second role will have the red color . What if I want to select then Tarver spreadsheet or entire sheet? What do I do? Well, basically uses cell stored, select every cell in this shed. So I quit. Okay, so not a big problem. What if I want to say that I want to, like, remove everything which is in the street? Me? I can say dot Coulier. Well, you get intelligence list when you'd use it as cells on. Let us clear everything. Everything has been cleared and now we know that we can give our range. That would be this. My range my books dot value equals I will see tutorials. And then you can see that those values that books has elegy trolls. If you had something but this you have the value Invisible cells wrote 20 and Fulham is like to let's say its value is Kareen. This is its value. And when you add value, you had double fissionable court around it. Now there's a screen, Isn't it cool? This is really cool. And then lastly, we have active. So my disciples I want to change active self color. I wouldn't say that, kid Cell door interior dark color equals will be read. So what will happen when I render is the active cell that is B one will have color red. As you can see, this is the actives this waas act yourself so it has red color. But what if let's do something fun. Let me copy this and go to sheet one. That is this shit on. I want to say honest Lecter shit worksheet on. I want to just pay sitting here, so let us see what happens. Okay. Uh, it's like this I grew up whichever select which ourselves. I go to a real change. Its color. Isn't it cool? Come on, this is cool, isn't it? Uh, okay. Okay, guys, thank you very much for watching. And this most whole I will meet you in the next drill by my 9. Introduction to VBA Sheets and Workbooks: Hello. And that's how guys welcome. This is the trail on sheets on what works. So let us get started. First of all, we're gonna go to all tacular now in here, and we're gonna create a new module and big. Okay, so first thing first, we're gonna see how we can an image it. Well, how can you had? And you should we can say she eats daughter on on. We can add a sheet using this. Let us see. We have sheet one now. Currently, we have sheet one If it just run this maker, we're gonna have sheet. Okay? The problem arises that the she too is before the sheet one. But I wonder she to after the shit one. Because if I add a new one the sheet three goals back. So we wanted in America that we want one. Then two, then three. How can we do that? First thing first, we're gonna delete it. The current sheet three entry to how can we do? Delete them using the baby? Can't say she it She to directly thought dill it. So it's up to you to say she tree or you can go directly. Go to the tree tree tree to date. Let's say witness rendez on your still it. And yesterday it. Now what we want to do is we run the sheets to be in numerical order sheets. Don't add. Okay, After that, you get cover off optional arguments in here. So you want to go to for after you're going to say after than colon than equals means I am accessing after and I want in this after so have argument, which is she? It's dot count of your sheets. So let this ruin. This is gonna say for of course, is going to save. Oh, because now to entry are diligent. 456789 10 along. So these are in order, we can access sheets by their index number one? No. So it's gonna go to shoot one and dilated. Let's see. Yes. Dilly. Now this shit is shit work. Now the good part is that we can really change. The bad part is when I delete this I have this confirmation message. I don't want this to happen. What evidence is up? Application. The just play Allers equals false. So basically my application that will be Ansel would not display alerts. First thing for us is gonna stop displaying alerts. It's not gonna display alerts or confirmation messages because I have already told this program did not show me any confirmation message or alerts message. When you turn your display Allers off, You always have to turn it on back. Now let us try, okay? Of course. Let's go to testing for us now. Trying. Okay, so now we're done with this to Okay, this was good. Now, in the last thing, we can change the active. So I love cookies. I can say Let's common this out. So now it is right. Give your dented active sheets. Now let us come to Facebook. Works. What? Books broke the same we can say. Uh, what works dot on. Let's it's gonna add a new work. Look, now you see that I have a new workbook that says book to I don't want it, by the way. So I'm gonna delete it or just close it. Similarly, I can say, for example, I had a look. Book number one When I Adebowale book, it becomes active. Broke, right. Okay. It's You should know that when it becomes active workbook. I can say active workbook dot close. So let's see. I press f it. Have it. It's gonna have a new book. No, Book three is the active A book. I can close it for you guys. There are a lot of things inherent which I can't cover. You have to find it by yourself. Okay? I see in the next real by 10. Introduction To Functions: Hello in a certain guys spoken to this journal and industry of we're gonna be learning about the functions. So let s get started. What is a function? Basically, if you come here and like some, this is a bunch. Okay, this is not function some. Really? If you want, find the product of numbers. This is a function or product on. We don't have a function for offer reasonable aceptar or subtraction. You don't have any friendship of that. Similarly, we don't have form and function for area off square. You don't have any function for a day of school, right? So what if you want to create a particular function, which I will be using? What, every time I want? I want to calculate some particular every time I want to calculate area or I My job makes me Catholic area every second time or every single day. Whenever means I want to create a friendship off myself. So what I can do is I can go to old at 11 and go to create a module and in here I'm going to say puncheon's or function name of the function that's ah area square on in this area square. I have a couple of variables. I have a couple of inputs That, for example, in this situation omelet length and width. Right. So what I can say is I can say lent as you double or as into your It's up to you, madam. Your number is smaller. Yeah, whatever. On another land off on it as again interject simple enough. And I want the that returned the answer to be so the answer The return to be if what, Friend? The return while you have to do is use this thing again. So this has became a variable. They have to use it to get a return. So I want to hear of spur to be equal to the Lent Times the risk. Okay, now, if I try to sporting laying here, first of all do and we find any form or area off square. Yes, we do. Fine. And we have here two emperors. One is just and the other one is this. And if you enter vegan 40 means we're getting correct number on education. See, we're getting a curtain. Um, no. What? No is all actually not this is not the whole or This is not all. Oh, I'm scared of something else, but ever. Okay, so now what? What? This is the difference between this sub, the sub that we owe more about on the function. This is the something distinct 50 or whatever this is. The difference between sub and function is that you can the return our function. But you cannot return. Our sub is it will return you a value. Now it returns me a value. Right? But this doesn't return me. I will. You can do stuff for me. For example. I can ask it to, uh, from your message books, and it will do that. But I can never ask it to return me a value. Okay, When you return a very obviously the return should have a type, right? For example, here, I'm returning. At what? As what? I don't know. Maybe I want to return to be, uh, basketball. I want to u turn to be double. Okay. Or maybe I want the return off this Toby bullying to look at results. So if I don't come here and refresh this insurgency, the return type changes. Even though this is number the reader, the result wasa number. As you can see in here, the result was a number that is the product off his left four times 10. That should be equal toe and physical body. But then I said to it that I'm a different type of return. I might sometimes want a 1,000,000,000. I might somewhat time want, um, a variant over ever. So this is one way of doing getting a product. Maybe this time I want a very and so maybe I don't know the type. So I can always us for read now if we'd come here working fine on as far as this is concerned, If you have same data types again, you have lent and send it of that. So what you can do is you can get rid of this and you can have only one lent with as interject means these two are as integers on. If I come in here again, that is yeah, no difference. It's all the same. So this bus introductions of functions. Okay, guys, that was all. And also in the next room. Why? By 11. IF THEN ELSE Statement: Hello And Osama. Guys, my name is momentarily and welcome to Decision. Today we are going to be learning about the conditional statement. So let us get sorry. What is the condition statement? It is the if then else that Medak be mostly used in Axle. So I'm gonna write are simple condition on. Let us see what happens. But I'm finished. So basically, what is happening is I have declared a very bill called input and I'm awesome, uh, user to input his age if the edges created and zero on is in between it in. I'm saying that you are a teenager number one. I don't mean that this is correct program. This is the way it should be. I'm just reading a simple for them to demonstrate if else conditions I don't mean toe offend anyone like you're 31. So your old I don't mean that. So in Birdwell is directed in 18 minutes. If we regretted If your age is created in 18 and it is less than 30 then your arm man, right? If your age is greater than 31 Oh Then I have to say that it is 31. It is grated and 31 than your old If it is some other condition than you are in for extract values Basically what? I mean that if I put my ass 200 it's gonna say in for effective value. So let us check our program. I'm gonna say to any it's gonna say you're a man. When I said 10 is gonna say you're a teenager, I'm gonna said till 32 it's gonna say you are all so it is bringing perfectly. So that is our program. I made it three short because I know that you guys know this program. This is really easy. This is in the exact itself, as you can see in axle work should if you go equals gift, then you see that there is if then else. So this is all the same. Just the matter. Syntex is a little bit different and everything is same So guys don't worry about it. It's not a big deal. You get used to it. Andi, I'll see you in the next true Thank you and bye bye 12. SELECT CASE Statement: Hello and Osama bin guys today we are going to be learning about the select case. So let us get sort of physically what is select case slept cases similar to the nested it, but it is really easy to manage legacy. Let us suppose that we have So we have a basic program say is that if the value of input is only, then it's going to say, Welcome this really. And if we put in put something other than early, that would be case else. It's going to say incorrect using it, we can put other condition. For example, again, you can say kiss in the case it is. Let's George, he can say message rocks. Welcome surge. So this really is it. Let us check this in case I input Ali. It's gonna save. Welcome, Mr Randy. And if I input George, it's gonna save. Welcome, George. And if I input anything else that Dracula curricula is in a state incorrect using it now, let us have another example which would be a different way off handling case or select case . It is get started. - So there is an infant books asking the user to enter his marks when they use their enter his marks. If the marks are from 0 to 32 he's gonna get a message saying you have failed. If it's 23 to 50 it's gonna say you have passed with Deke great and again. So if the case is not among these numbers, that is going to say in ferric input that just check this, let's say 55. They're fast with secret. And if we see 55 to 51 to 69 so 55 comes in here is secret. And if I type 25 is going to say you have failed and if I type 103 it says in 30 in book this is a really good program so that just go to the third way So now we are going to check whether today is a week there or no, you're gonna say in case it is Saturday or Sunday. Well, basically, what I wanted to show you is that you can have multiple conditions in here. It is. Go to the next problem. So last example, which I have taken from a particular upset on that example, is this. So we can say so. If the value is less than 100 it's gonna say North if the value is less than 200 despondency south. If the values lesson traded this gonna say east if the else it's gonna say it West simple as that. This is the condition, Let's say for a few nine. So you less than 100 days north. Let's say 105 is gonna say sounds because less than 200 is south. You guys outside in the next room. Thank you very much for watching. 13. Do While and Do Until Loop Statement: Hey, I said hi. My name is moment on the and welcome to this field. Today we are going to be learning about swallow. So what is? Look, look, it's basically a way or a statement using which we can repeat our court number of times. If I want to repeat my fort 200 times, I would use look, so let us get sorry. The greatest, impulsive program saying that Tim s interview her on I equals, I'm gonna say do while I is less than it's winning right on. I'm gonna say Look, means I'm striking my condition from here and are ending my condition. So everything in between due to destroy you with room when this is true, when this is true, I miss five is less in 20. True once when this is true, then it's gonna run When it becomes like, 21 is less than 20. No, it is false or twenties. Lesson really know it Discourse is going to stop, right? That's what. So let us see. I'm gonna say cells. I love one either through I love three. Then when I equals, I post one. So we're gonna income and I want to see. Now, if you look in here, I would love 1234567 until 19 Because I said it should be less than 20. Once it becomes 20 is going to see is 20 lesson training. Now it's gonna get out, that's all. OK, I hope you understood this. Really? This was really short through. This is all about. Do while we have do until you can see two. Until now, no do until Same thing. And we say that I will squirted in 20 only for a run This it's gonna bring me indifferently what is happening. Basically, until checks when I is, let's say five is grated and, uh 20. No, so five is not brilliant and 20 so it's gonna pace this. So this is one under This is false. It's gonna keep raining. But once this becomes true, for example, 20 one is Scriven than 20. Yes, that is true. Once this becomes true, is going to stop. So both condition differ. It's up to you. But most of the people used to while until is no deals because that is the same thing. It's up to you. I don't mind If you want to use this, it's not a big deal. Okay? So let us see if I want to put to on. I want it to be 22. I'm a ring. This is gonna say I love you. Attended. Are you guys out to you in the next tutorial? Thank you very much for joining me on and see you. 14. VBA FOR Loop statement: Hello and welcome, guys. My name is more mentally and folks in the previous ritual, we have learned how we can use the do I Look, today we are going to be learning about death for Luke. And the four loop is the most frequent Luke that you will be using. So I just get certain. Well, basically, today we are going to be doing our program, but a little bit different. We're going to go to the developer tap, then click on the insert, and from the drop down, we're gonna select the Activex control. Okay? No, let us right clicking her on. Let us say, Come on, object at it. Here. I'm going to see. Click here. Now exit the design more By clicking on this Now you can click it. We're gonna go back to design would and double click on this so we can court this book. You know, we can write whatever we want in here. We're going to say, for I equals 12 It's a 10 and next. All right. Means my for lip is gonna start from one on. It's gonna continue. Understand? Okay. So let us say cells. I Rachel, when I thought, Well, you is equal to I. Basically, what I'm saying is that when eyes one at the value off sell 11 that would be did Should we call to one then, when I was to the value off, sell 12 should be to let us see this. You're gonna go back on, uh, come out of the design more and clicking here. No education. See? One want to do treat 344 This is really easy. Or you can do a lot of stuff with this. For example, you're gonna said that French A and I dot interior the fuller equals three b Britain, get to see what happens now We're Guinness. Click this and this becomes red. Take this part again. And instead of interior where there is a fund, the color equals we be What? No, I just try this. And as urgency, this becomes white. Okay, guys, thanks very much. And I'll soon the next drill. Bye bye. 15. Message Box detailed: Hello, Anna. Sorry, guys. My name is Mo Mentally and welcome. Today we are going to be learning about that message boat. So let us get started. Basically, you know, message book. But it is not the one which you think it is. It is the same. But listen, we have seen that it was a message. Parks, I love cookies, cookies, cookies. I don't know the spelling. I think it's double up cookies, cookies. And if I just read this was gonna say I love cookies, but what is this talk? I don't get that. I want to change that massive my first offensive. But what does that mean? I want to change that. Well, basically, I can do that. The title. When it says title, you say, What do you love? Okay, that does your title. Let us check it again by clicking here. What do you love? I love cooking well, but what is this? Okay. I want OK and cancel. I don't want just Okay, So what I can do is coming here and in here I can say it. VB lets it can cancel. And now another strike. It says I love cookies. OK, And cancel. Okay, this is good, right? If I clicking here there, it doesn't tell you whether it's an error. Whether it's an information, whether it's something like that, it isn't tell you that now we can program it to do so. So what we can do is we're gonna sit plus on began Save Phoebe, Let's say believing information or will be critical. It's up to now. It says. We've been for me as you can see it, this stain for missions symbol. Or you can come in here and make it maybe pretty good. And it says now I love cookies, but it doesn't. It gives you an error like symbol. Is it a drip? Well, I'll tell you something greater. Let us suppose I have game tries as integer, and I make this equal Detroit's When I truth OK or cancel, I say Tries equals This is our message wife, because it tells you when you're making it equal to something, make sure to put parentis is apprentices are very important. When you putting some well, you liked result off this to a tries, so let's see what it becomes we're gonna say. Message boards, tries twice, tries again. So I love cookies, I say. Okay, so what? It Retton's one minute, okay? Equals one. Just well of cookies canceled. Cancel is too. Okay. So I can make appropriate tries. I can say if price equals one, then message works. Futures. Okay, else message boards you chose got canceled. And it now, let us see how it works. We're gonna Cleo gives you an error that I love cookies. This is a great editor, by the way. If I said okay, it's just you chose okay now, so bad. If I say cancel is gonna say you choose or you chose Cancel looking. By the way, these are the others. For one, it is OK for two. It is cancelled for a bird because you have a board retrying Yes. No. And these are the numbers for tours. Thank you very much, Guy for washing. And also in the next room. Bye bye. 16. Input Box detailed: Hello and Osama. Come, guys. Welcome again. So today we are going to be learning about that input box. Well, basically, in purports is input box. But first off for you need a variable. When you put something, the value is going to that first book. So we're going to say it then. My bar as a string lets it and you're gonna see the value of my war equals input box. Now here comes Stop first thing top your caution in here. Basically what did your what is your name? Let just just see. How does it work? What is your name? And this is because we have Microsoft excel in here. So we want a title appropriate title Gonna say OK, so we have an appropriate title which is name Good. Now what we want. We want a default text. What do you mean by default? That's it. Okay, Now you will see that it says enter your name here, Doctor Thought so. It This is a default text when you're taught when you type something, it's been a areas that strange. Okay, one The value equals my war. And what we see is what is your name has him meds. Ali seen. It's gonna show you Muhammad Ali s. Okay, guys. That muscle. And I'll soon in the next half year old. Bye bye. 17. Getting Started with Forms: Hello And Osama from guys spoke to him. So today we are going to be learning about forms. So how do you add a form? Basically, you go in here, rightly insert or from the top menu. No problem. Then you say usable. When you click on your phone, this gets popped up or you see this. Okay, now, first thing first you need to know the toolbox. Well, basically, the first element is cursor. The second is called a label label is a text. Ryken said, begin saying welcome to a little tea tutorials, not check this. It's gonna say Welcome, generals. Truth. Now we have textbook Textbook is something we can write off, but we're gonna be using a command button. It is basically a button. Okay, we can rename it by clicking and then changing the name or by going down looking for something called caption. Okay, so this is the properties of window. So what I want to do is many work like this. A message should appear saying Welcome, Mr Ali. They will click in your recent message. Looks welcome, Mr Ali. Now, that district this now this is the use a form. If I click on this. It says, Welcome, Mr Elite. Okay, isn't it dread? No, it is going back. We want to change this text to be to look beautiful. Don't be. So we drag it until this and we actually resize this. After that, we go to the properties window, and first thing first, we're gonna change the text. We're gonna say from front, they're gonna go in the front, Uh, Brenda and we're gonna make it bold and made its text kind of big. Lets it 22. I can't district or really big thing. Let's make it, uh 16. Okay. And now 16 is good. No, me want to center it now we're going to go down, and we're gonna say it is to be at center. Now it is centered. Now we can eat, even change the color. We can say foreground color and we can change the color tool. It's ready. Oh, we didn't select this or what? Respected was this. So it became rent. Let's big ticket back to this. And let's select this and changes color to red. Now, this color is right Now. What you find is welcome to L. A. To trails in red. Isn't it beautiful? You guys discuss old for destroyed. I'll see you in the next shuttle by 18. coding close button and adding Text Boxes: Helena Simon from guys. Welcome to district in disagreement. We have same form as a previous, but by the command Burton One became save and second became close. So we're gonna learn how to be can't program this close button clothes are for. Well, we can do something called unload meat means true loves me. Okay, Now it's gonna unload. It means close it. Isn't it good? Let's design it. We're gonna say no. What we want to do is add the text boxes in which we enter the text. So this is often normally just programmed this one. Now let us try running this. So basically, I enter my first name. That is Ali. Andi last. Now that is a thing I click on Safe indigenous appear in her. Yes, it. Well, it's up to you to the way you want. Redesign it. We're gonna make it really good. So wait for it to be completed. 19. How to access last row of worksheet in Excel: hell on this album, guys, Welcome to disinter Industry. We are going to be learning how we can at our data to the next available on which weekend. Right? Order. This is the last populated row, and we want to access their after it. So we want to excess row for how can we do that using programming? Because when we want to add that, if you want to add it after the previous sitter. Okay, so how can we Dough. That is really easy. Let us see. We're gonna say so. Basically, I am coming in here. Andi, currently where I am is this road. Okay, So currently, where am I at end off the road. So this means and off the dirt. And then we say eggs L Oh. So where am I now? Currently I am now up in George on George on Rotary am on currently populated or country last populated rock. Now he said, ought dro means I want a number off this rock. First we're gonna say doing so basically what this formula is gonna show us it's gonna show has three Andi with trees, then lastly populated and we will act less one So, basically, we're gonna go to row four. That's it. Okay. Is the lettuce right? Try this one. I'm gonna write a basically, I'm saying so. Basically what I'm trying to say it's how important the value off first name and I want. Oh, it to go to the last road. That would be this for and column one. That's so let us right this. I'm gonna try John and John is now in here. Isn't that great? Okay, what if I want to? Now have the last name? Let us try. This first name is, uh, Zone C ne. No, it says John Sr. And similarly, you can add at the columns to Okay, guys, that Russell. And also in the next room. Bye bye. 20. Adding Nationality and Date of Birth to project: a sound, guys. Welcome. So in this form, trail number three, we're gonna use form to add this data. So first of all, we're gonna name this. Use the phone. We're gonna name it as data country and make sure that there are no spaces against. So now it is name as jet entry. But this it says form one. Instead of that, we go to caption again, as we have learned. Okay, so it says did not entry fall. So know what we want to do is we want to program the same Britain to go in here and and the data So that is coming here. Go to the save button. Now what we want to do is we're gonna say so. This is our last row, as we have learned in the previous trails before. Further going let us do One simple thing is that is changing these things Name. This is named as textbooks one. And we're gonna say txt first and this is named as textbooks till they're gonna name s txt . Last name. So we understand that we are extinct. Last name or first name? Techstars? Yes, If you have to. You can understand But if you have turned that it will be really problematic for you. So it is really important for you to name these things. So they just coming back in here? No one. I'm going to say it sells. So let us try this. I'm gonna run this form. I'm gonna name it's it. Tom. Jerry, And let us sit this. So Tom and Jerry come in here. Okay, Now let us on the date of birth and nationality. No, I just come in here and just co pd's too. So we're done. Let us try this. Did that really philosopher or something like that on date of birth would be is. And he's Indian, African Indian. So let us Thetis and his death becomes okay. Good. Lasting with no, the least is one when we come in here and press tab first its own safe than its on cancer. First name, last name, then nationality. Danburg when we pressed up. And this is not good, we want it to be in order. So how can we do that? Well, we go to view and go toe tab order. And now what we want to do is first we want access to first name, then Last name. Okay, so now let this check this cap cap top top top. Now it is good. Okay, guys, that was oil. And also in the next room by 21. Adding Combo Box: they come. Welcome again. So let us get started with today's Trudeau. First thing first, we're gonna come in here and have something called nationality, and I want to name this rain as national cheese. Okay, I'm gonna cooperate this on. This is our name. Drink. So whenever I access the nationality is basically I'm accents. And why do I want that legacy? I'm gonna goto altar for 11. Instead of having our textbooks. I'm gonna get rid of this, and I'm gonna try to find something called combo Works and go down and find something called Roe Sores. Okay, let me draw source. And in the row source. All I need to do is drop my nationalities. When I drop this on, let us see. As you can see, I have all the nationalities in here. Isn't that great? Okay, what if you want to add extra and nationalities, let us say Bangladesh or you say Bangladeshi when and how you have this. Okay, Now you have this, So if you try to find it in here, it doesn't have Bangladeshi. Why? Because even though we wrote it here, we don't have it has our named range. So what we can do is we can go to our former lives. Goto, named Ranges on the planet on, basically added them again. So now this is your range. So basically one leader problem is that you get nationality in here. So what you can do is again Come here, go to edit on. Basically start from the country's. Okay, Now it says Pakistani Indian and stuff like that. And you have Bangladeshi in you should have Bangladesh in here educates You have Bangladeshi in here via gonna go to developer Tab Goto insert and we're gonna use up form. But enduring tried this interruptus in her You don't want anything and basically we want to name this as now. What we want to do is create a module and we're gonna say in here you type in data entry door. So So let us come in here on a side. America, that is opening data entry and let us now click this So it's gonna show us this form and we can disclose Sure. Close shirt. Let's say first name is, uh basically we encourage and her because the the name has changed. As you remember, the name has changed So this is not This is CBO city or nationality means combo box nationality that just come back in here and let's say CB now that destroyed again. That's it. This and you get that letter that school, isn't it? It's The last thing I would like to do is I would like to change the tab order because nationalities and on and on, we want it to be after txt Deep data effort. Oh, guys that Russell about and CIA. 22. Adding Gender and Contact Number: Hello and welcome, guys. So this is our project. We are gonna add two more spaces to more fields or whatever you want to say. These are caught by the field, and those fields will be gender on contact number. Okay, so let us get started. We're gonna go Toa alta 11 on. This is our project. We're gonna cut this through and like this one on control V. So it's gonna be inside this box. Well, this is a friend. This is off boards in which we can add similar things. And this is really good thing. If you want to use this, I'm most of the time. Look, I love to use this. Now. Go to the properties. Video on in the caption section Changes toe select gender. Not from this section. Shoes, male. Then go to group name. Name it. Gender said the 2nd 1 Same name. Gender. Well, why they do that? Because we can have a lot of them. And for example, we can have male female. Then we can have ah, debit card and credit card. And you want to make tries off between doors? This this is different group and that is different group. That's why we do that. No, we need to name these two. Number 10 B. Means option button. Obi, Let's say mail and then we have will be female. Okay, Now, what we do is we go on save and we see if oh, be male. Don't Value equals true. It means if obi meal is selected, done. If all the mail don't value is equal to truth, then what? Then I want the value and I want to cope with this. Then I'm gonna say that Good toe five. And I want that value to be equal to Yeah, House. If so, let his check this. I'm going to select meal and I'm gonna try saving and gonna send me If I see female, I'm gonna try. Saving is gonna see female. Isn't that great? Okay, now this is working. Don't we need another thing that the thing we need is a contact number. So first thing, we're gonna do it in a different way. It's gonna say contact number. So I'm gonna double click on this, and I'm gonna go up, and I'm gonna say public contact. No strength. Basically, this contact known will be accessible I'm I can use it in here or here or in here. It's up to me to I can use it in every sub in this module. So I'm gonna say contact now equals I'm gonna cope with this on. I'm gonna make it six on. I wanna change this value. So corn tack number. So, basically, whatever I enter is gonna go in here. Okay? Let me try. I'm gonna say ho conduct number is 3594543237 That would we mind? I'm going to say Okay, I'm gonna click on Save on. I have the gender, the contact number off course. I don't need to deal with this, but I wanted to show you something different. That's why I use this guys. And by 23. Recreating project using Active X Controls: Hello, guys. Focus. So what we're gonna do, we are gonna try making a similar use a form, but we're gonna do it using Activex controls. So number one we need what we need. Three for three decks, boxes on one combo walks. So let us get started. What we're gonna do is are three textbooks is this would be textbooks for an added in here . We're gonna set this one and go to properties. And in the properties you goto background color and here cultures active this thing similarly, I'm gonna choose this double clicking here. So we're gonna go to phones, go to safe and copy every single thing in here. That would be this on. Bring it back. It formed one. Now we have this. You're gonna have same name. Go to developer. Damn girdle Design Lord on, select this one Goto properties. And this one is txt. First name. We're gonna go in here and this on. We're gonna copy it. This going here? Onda, come back, Double click on this and we have it is in here. We're gonna go back. We're gonna double clicking here again. We can cope it this time to when We're gonna go in here. Okay? So we have everything. Good? No. You want to align the stinks in here? What I will do is I'm going to select the East things in here on. I'm gonna form it a line and left Atlanta. Okay, We have all books, so let us try this. We're gonna go get out of design. Moored on. You're gonna say from here we have nothing up that is back. What we can do in here is go back to design world, right click and or the properties and the properties. We have something old list, Phil Range. So we need to plant type in your nationality. Okay, so we have been in here. Now let us come back and we have the nationalities. I'm always Pakistani. I need to check their strokes and I t security. 59433 That's my contact number on. I'm going to say I'm a male, and that's all. Now it is working perfectly. OK, guys, that was okay. And but why 24. VBA AutoOpen: Hello and that's our guys. So we have. I'm booking her. Did this car Global One Let me try this one. When I opened this I get and input books. They asked me to enter the use an M. I enter my name and that is incorrect. That's why it closes. Let's come back. Let's do the same. And let's now this time enter the correct use. And then that is really on. Welcome. Literally. And boom, it is working. So how can we do that? Basically, it's really easy. All you need to do go to alter a little lemon and the first thing first you need to have this line as it is in your much so you need to have a module. And that model should have the name Auto Open. Okay, Make sure to have exactly same name. So I declared a very but called dim. Use the name high strength. No problem. We get the user name equals whatever do use the name is whatever you enter it with going here. Let's say I enter early. So it's been checked. Is using in Bali? Yes, it is early. Then message. You're welcome, Miss. Really else If it is not only then is gonna say book books don't close. It's gonna close the book book and then this is the end off. If so, if you want this, if we want something going to happen when the book opens, you need to have this. OK, maybe you want something like something flashy, something welcoming something, something awesome. It's up to I'm just giving way. You can do that. Get guys out there in the next room. By what? 25. Object Variables and With statement_ Learn to stop repeating: Hello and s a my guys and district. We are going to be learning about the object. Very Bill, What is arbitrary will basically object. Wearable means you can, Tim or make or declare a sheet as a verbal you can declare arrange as a verbal or you can declare chart as a very well. It's up to you. So that is God object. Because object is, I think, in exile. So anything you want to declare, we can declare it as a very But we're gonna learn this industry. So let us get started. If I say she x, then I just cope it. This on, sir dot value it grows grumpy people. Let's just say this we're saying right, you don't mean it on Duh. I have to go to a lot of problem to be to do this. Why am I doing it? Maybe because I'm on chief to okay, I want you to have working here. I'm a macaroni. However, bottle in here when I click overdone, something should happen on she quit. So I have to do this because the way macro works or module it works is you have to be on the she to do it as this. You have to reference to that shit already have to wrap. You have to have reference to Detrick. Actually, this won't work. Because if if we are selecting it, we have Toby, introductory it. Let us delete this. So basically, we're going to start from there we have here. We're gonna press it on. Go through this on. Okay. And come back. Nothing is here. Okay, Now let us go through it again. But until this Now, let's come here. And we have grumpy people, and it is working perfectly good. No problem. That is awesome. But the problem is I had to talk this thing away over a lot of time. So what I can do, I can say dim my formatting. Uh, strange, because this is a range, right? And you say, set my for mating equals this region here. So basically, all you need to do is now use the set for mating instead of this huge thing. Doesn't it make her life easier? Okay, so now even run this. Go through it. F eight F eight f eight, uh, grumpy people read white, and now it just clear to make your life more easier. You can use the vid statement. How can you do that? Well, basic instead of having my for mating. So what you can do is you said you can say vit my formic and let me just have them out, OK? Basically, what is happening? I'm saying that my former team should be virtually in here, in here, in here, in here or in her. So actually, we see we don't share. But actually, when the program go straight, it praises distinct to here than here. Then here, then here. Okay, so let's go through this program again. F it. Okay, so it says Currently people seem think no problem, but we have actually reduced the repetition. Repetition is really a cost. You have to reduce it. You have tow clear it, you have toe control it. Guys, this was all out saying the next drill and bye bye 26. Opening Notepad With VBA: Hello, Anderson. We guys welcome to do. You're gonna be learning how we can open a North Platte. So let's get started. First of all, we're gonna create an activist pencil in here, right? Clicking here and rename it. Let's see at it. Bring it on. Open in no time on what we're gonna do it now is where double click on this. Now open no parent. Let's say shell, then double courts devil courts than in here. We want the path, and the past is let us say not bad. Right. Clicking here on Goto Open fire. Location on inside here. We still have opened fire location because this is what this is a short cut. So we need to go and open fire. Location on. This is the location of the front. You can just write you in here on. You can go to detail, actually, security on pick this up. Um, now we're getting here, and that is all. Now, what is gonna happen is if you come back and get out of the design more and click on this, it's gonna open us. Untitled north pack. So now I'm gonna close this click here on its gonna bring me one. I'm gonna click again, is gonna bring me in a villa. So now we are able to open Lopez. Similarly, we can open the what we say calcula. What we want to do is what about the way you don't need to write all this code to get to know pad For some reason Not bad. Just write a note on your e Z on. It's gonna open. Okay. One way is just you go to the I don't want to solve it. Save it. So one way is that you go to the actual fire location and open it on one other way. It's just you open it like this. I don't know. Maybe it's a variable or something inside the system. That's what you are able to open this. Okay, now the door, it's open. It is. Oh, by the way, there is one thing else. If I go and go to old lemon and foot hole mine here. Andi, I said maximized. Focus or normal for this normal focus means it's been open on. You will be able to directly into stuff in there. Now you can see I can open directly. It when it was coming before, but it was coming hidden. So now it's normal. Whatever you have, a love tries to choose from. But this is one that is mostly used. Okay, uh, can be open capital it? Yes, we can open company that. Let me just Oh, yes. Sorry. I'm gonna sit calculator for couple it. I'm gonna say shell, and that's a cat litter. How few layers exit. I'm not sure because I didn't open it before. Yes, I didn't know. I don't know. Okay, so it's not the correct path. So what we can do is they calculate her. Andi. Oh, it's not opening Hugh. Layer on. Right? Chicken here. It's no opening. I don't know. Let me say cow. No exit. Is that what? Yes, It's top dog fancy. I'm not sure I wasn't able to get its ah location. But any other programming location, you can open it or you can use thes names, which are actually I think there are variables which are defining system. Whatever. This is how you open them again. You're gonna open Kifle later. You can open note. Pad on. Lastly. What about if you want to open a particular file. That is eggs that exists in the system in the Lord parent section. How can you do that? Well, that is also easy. Let's start again. No problem. Sorry. On we want to open a particular file, which I'm gonna create right now. I wanna see your file on a text file, and I'm gonna say house. Hello. Hello. And I love my country. For some reason, I do its my country. And in a way, so this is the file's location. Where we're gonna do is its location again. Security section on the Get it from here. Get everything from here on. What we're gonna do is go back inside this on being a right kid and see what big right bringing, right? Um Then let's say my file as street I'm gonna do is on the center line file. This'll efficient in here. No probes. And then we're and then in here, I'm gonna sit on and write my five. I hope this makes sense. If it doesn't, I'll make a sense for you, or I'll make it sensible for you. I'm gonna click in here on if a debug I'm see that I have a problem inside this I am trying to open the file, but it is no for some freaking reason. Oh, you need to spacing here on If I now run this, it opens my I love my country on if it come back and re read it And as you can see, it opens the tickler file says hello, Don t Extend says I love my country. Okay. Is there any other way of ah beside creating variables? Yes, There is a simple way that variable is really great. Tries that person you might want to change itself Change it here rather than going to fall for you. Maybe you have thousands of lines, so it's not a good idea. But if you have a single line on you want to just open it. You can use it like this. And this is the foreign you want open it with. And this is the file you want. Open this Brexit Every program that is only a computer. So the file particular program we want to open in the particular program file You want open using that program. You can do it using this? I think so. I tried a lot of them and they work. So I am assuming eight months ago. But you try it if it doesn't work, but any, is that? No. OK, so let's come back and you can open it right now. Okay, guys, that was all. And I'll see you in the next trail. Bye bye. 27. Make VBA Speak for us: How In a Some guys. Welcome to the cereal industry, Turia. We're gonna make until speak for us. So let's get started. Right Click And we're gonna go to insert and sort of user form. And in here we're gonna insert a caption. What year? A caption. Which is going to say it. Welcome. It begins. Right on. Uh, we're gonna do some form running to it. That is all. Not a big deal. I don't need anything special. I'm gonna go inside here and inside a click section. I'm gonna go and choose O R. Click on initialized, okay. Or active it. It's up to you begin to activate or initialize. I'm gonna truly initial. Oh, Accurate. Sorry. Okay, in here, We're going to say application door speech dots P. And he's going to speak. What? It's going to speak. We don't label one door caption. Whatever is in the capture is going to speak that great. Right? Last Try this. Welcome. Now, uh, now what we're gonna do is we can actually changes and put our dicks in here similar to saying like I love cookies. It's our text. It doesn't have anything to do. Whatever is present on the I love cookies. Yeah, I do to wherever. For maybe. Let's save this on. We're going to save this thing out. We're gonna save a deck stop. Actually, I'm let me show you one. Actually, I have created one. So let me show you that one. After we have finished this, this can't be bay. Speak. Let me double, please. Welcome to lt tutorials. L A T tutorials is my YouTube general, so l or let's help wondering Tiefer together and tutorials. Stupid name. I like it. OK, so how is it working? It's really same. First thing is, it's gonna wait for one second when it's in. Now, Tom Bellow and it's gonna be it for one second after waiting for worst ticket is gonna say Welcome toe l l teach Furious. Great, right? I can make make it with for, like, three chickens that can be long waiting, But let's see what happens Reading. Reading Welcome to l lt tutorials. Yo, now it works. Were you guys that was old and also in its true? But my 28. How to paste data to new workbook and save it: hello and a sunken guys welcoming, distribute are going to be creating a new book book adding this data to the new book book and we're going to say that simple as that. So we're gonna go to older Lemon, we haven't Mataric our new macro We're gonna first in first within is an application dot screen updating equals false, so v don't get screen updating. So again, we're gonna say education don't screen updating equals true. So in Big Green, these two, we're going to write our got We're gonna copy the data. That is this and this ring has did being named as access. So we're gonna come in here and we're going to say this workbook dot sheets and they're gonna say students data dot brained naturally access Don't cook a Then what we're gonna do is after cropping this, we have to add a new workbook. We're gonna say work work dot So let us try this. We're gonna click on this. A new workbook will be added on cheat. One is the active sheet. So we want to put our data into sheet one. That would be the active ship. So we are basically gonna say actor sheet dot Pissed. So we're pasting out it after the active shape. Now, after besting the data, all we need to do is see if the reader how can we do that? They're gonna say active vote work. Don't save as on we're gonna save it as in my USB drive. That would be l. We're gonna cope it. This we're gonna say save in l and Save it has, um, test door X l s X on. Let us see this. Okay, now this is her. This has been served in here as test dot Excellent sex. Now, the last thing we actually thing which I forgot. Waas. I didn't close this. I saved it, but I didn't close this after saving it. I'm gonna say active vote work that close. So now this will be closed after being created. So what we're gonna do is we are gonna try this. This is going to give us an error because it's going to say I find name already exists. Do you want to replace it? We're going to say yes. So now let us delete this Basically on debts. Come in here and let's do it again. So this time what happens? I don't get the new workbook. It doesn't appear it is. It has been closed on the far is here. Okay, guys, that's all. I'll see you in the next drill Bible on my 29. Creating Duplicates exists program: hello. And as our guys will come today, your check out the check for duplicates. So this is our simple list off things. For example, 11 and stuff like that on. We're gonna click on this button and we're gonna try it one. So it's gonna say 22 duplicates and sit so you can not at the data. But if I type I love on and add, it is gonna have it. And if we cut down, it's gonna say I love. So this is a simple program, but its really useful to check the duplicate. So if you go toe old half a lemon, we have a simple beautiful A common but and our textbooks S O. This is a small program. Let's just double click on here. So this is our program. I'm gonna remove everything, but he's too. Okay, let me move this and I'm gonna say exists. Equals up. Vacation door broadsheet function means the functions which are available on wood should sheet like some count and stuff like that. I'm gonna say a worksheet. Fun. Chew on. I'm gonna say thought count if count of what to count. I want to wear to count out the count in column one. Okay, What do you want to count in column 10 but you have to say, this is a range. You have to tell this that this is a range. Okay, What do you want to count in this column One. I want to count the number of the value which exist in text books. One. Really? So let us try this. I'm gonna clicking here, and I'm gonna say, John, I'm gonna say our data it's gonna say to exist to of Jan exist. So I'm gonna say, if exists is greater than your Mr Do. Because exists, Ben message boards. So if it doesn't find anything, it should exits up and clear the books else on here. And if I'm going to say range a one on you, if you want to come clear the books afterwards, Okay? Then let us try this. We're going to run this and we're going to say it's a one. It's gonna say 20 to duplicate exist. Good. What? October one duplicate exist. The guys That was old hand. I'll see you again. Bye bye. 30. How to delay the code execution: how honest are organized today I'm gonna be showing you how you can make your VB accord vet for you or delays the execution off court for you. Or sleep for you. How can he do that? Now? We have a situation saying message books. Arm Ali on massive brooks. I'm still early after stool seconds. So if I read this right now, it's as family on. I'm still early. After two seconds, the court didn't wait two seconds. So this program is actually a problem. We have to fix this problem. And the way to fix this problem is to wait two seconds. So what kind of door is Aiken? Goto application? Don't wait. So wait. But how much time? So we tell it that way for, like, maybe two seconds. How can we do that? Is it now? Means whatever time is now less add to it. Time value, home in seconds. You want to add to it? There's air, Georgie. You don't want to. I had hours or minutes. You want to add only a second sort of the second. So now if I read this, it says, Ah, Millie. It's for two seconds and it says I'm still early after two seconds. Okay, There is another way. Actually, the way that way is really easy. Okay, so you create a new module. Actually, you can put it here. This thing in here, Declaration off a sub card. Sleep which you are. Bring me from the colonel. 22 library or current 32 door. Diello. Two more specific. But we're gonna use this current job, and we're gonna Its argument is milliseconds. So we have to give it milliseconds for it to sleep for us, or I have read to wait for us for two seconds or they stop the execution of program for two seconds. We're gonna say, sleep on in your witness it 2000 because simply you are programmed. So I don't need to be to its specific, but 1000 milliseconds equal one sec. Right? So similarly, 2000 milliseconds equals two seconds. No, we were in this, and as you can see, it's working. Okay, guys, that muzzle and also in the next room. But by