Excel VBA - Learn from very basics of Macros | Sai K | Skillshare

Excel VBA - Learn from very basics of Macros

Sai K, Instructor on Skillshare.

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
16 Lessons (1h 18m)
    • 1. Introduction

    • 2. VBA Developer tab and User interface

    • 3. Create a MsgBox

    • 4. Record macro tool

    • 5. VBA Objects, Properties and Methods

    • 6. Range Object

    • 7. Working on Worksheets

    • 8. Working on Workbooks

    • 9. VBA Basic Variables

    • 10. VBA Basic Data Type Variables

    • 11. VBA Range Object Variables

    • 12. Using WithStatement

    • 13. Loops using For Loop 1

    • 14. Loops using For Loop 2

    • 15. For each loop

    • 16. If and Else If Statement


About This Class

Welcome to the course on Excel VBA - Learn from very basics of Macros Course!

This class is completely targeted towards basics of VBA Programming.

I have designed in a way it is easy to understand each topic with shorter videos.

Learning VBA programming enables you to create an user friendly interface with your system to automate your daily tasks and this course is always in HUGE demand in the market.

This course does not require any programming knowledge of macros.

Only Requirement:

MS Excel (2007 - 2016)

This Course is for:

  • All Excel Users who want to improve their productivit
  • All Employees who wish to gain knowledge on macros
  • All Users of Excel with basic knowledge in Excel

So let's begin our Learning Journey!!


1. Introduction: Hello. Welcome to the course of Excell Baby automation. Firstly, thank you so much for joining the schools I am Cypriot on. I will be your coach throughout the video. My main goal is to teach you in a simple language on the best parties. You need not be an exception to take this course just basics Excel skins will do and you can start. Of course, if you just have in store Excel 2007 or Greater Washington your system and you'll be learning how to build my gross from based according a basic micro to lighting a complex one . And also you'll see a lot of examples in the ongoing we do. So let me tell you what free beer stands for quickly. So maybe a stands for visual basics for application application means all amis office tools like Excel Word, PowerPoint. All of these tools use common programming language card, baby. So now you can ask me where this programming language here. If you see any programming language, it will be returning course. Likewise, baby, also use the score squad Micros. This has set off instructions which are returning, maybe application. So do some of rebate is basically an editor wearing you can keep on my thing with the help of micros using a Miss Office applications. So by this course, it will help you to create our own micros so that you can bring your excel skills to the next level. UN. Become much more productive on efficient, as you can deliver your task quickly. So now that we have known for his re B C in future videos, how toe execute them. Thank you. 2. VBA Developer tab and User interface: Hello. Welcome back in this video. Let's see how to end of Lopata on also been alone how user interface off baby look like so in excel Bitey for never Lopata will be invisible on December so we can go on click on Excell office button, click on exit options and click on shore. Develop a tab in the ribbon check box. Select. Okay, now you confined. Develop it on next to beautiful. I've showed this in Excel 2007 bushel. If you have another Washington like Excel 2010 or 16 you can right click here on the ribbon and then click on Customize Ribbon. On Select will be develop a tapping. There is a slight change that sort. So once you have developed a top blessing, you'll see it's visual. Basic sunder, it says, Ah, launch visual basic editor. So this is where you can cite writing your cold ones. You click on this or by pressing short cut key oil Place F lemon. You can open it. I'm going to put this on, and this is called user interface off baby. So for basic set up, you should have project window on also properties window present If you don't have these two, let me show you how you can open it. You can open from beauty You can see here Project Explorer window on properties with or by pressing shortcut key. You can open these two windows. So once you have these two under the window you another thing. You should have this leave you more Do so This is a place where you can nighter courts 200 . You can goto instructor and click on more. So I've created model one here. Now you can see in Project Window You have sheets. I know so models So in BB sheets are coy objects. I know. So if you see properties window If I click on sheet one, they don't show properties off it in properties window. Now I have more deal Also, I can change the modeling by going toe properties Window Celik Model one Here you can change the name can do some descriptively on the name should be like Suppose I'll give first macro first under school Michael and he so you can have so I have not used basis in my modelling because it will not allow me to enter spaces on. Also, you cannot have numeric values in the beginning of the name. So there's two things you should keep in your mind Value. Name your body. So click on this. You can activate this. So next thing this which we can just start writing off course over here. So that's all for this week. 3. Create a MsgBox: Hello Welcome back in this video. Let's see how you like Micro court the baby in the tournament. This will be a simple core adding message box. So let's get started for this You have to open visual Basic editor First on, you should create a model for this. So in the last video, I've already short how Toe ad model. I'm going to use the same body which is quite first. Michael. So you you have not had created you can create by going toe inside me New select model. This would create one for you. Click on First Micro morning on to write a court in VB. Generally, it will start with the procedure so you have to start with the procedure Coy So which is quite sobriety, right? Sub on it should fall over your my group name since I'm showing him toe message box and right macron Amos, say hello See under school I'm giving a descriptive name so that whenever I open this microbe and see and stuff before leaving the court itself I will get to know what is the micro doing for by single microbe name So after this I'm going to hit enter. No, you can themselves and so is automatically and don't buy the We on also has endured brackets for us. So after some procedure, right, your macaroni and hit enter on in between. It's a place very construct writing your metrical so in the name But you have given there are few rules you have to keep in my firstly, it should not start with numeric values and also what you don't have spaces to separate. Your words that can use underscores onder Special characters are not allowed to use in this name. So let's see are cold. Yes, indeed. On him you can hit that toe right enough order So all of course will be, you know order. So I write my search box peace. So after giving space, you can start writing your working double cords. So I learned double course and say hello. Close the courts again. So this is my message box called look like message workspace on the world. What you want to give him the message on? If you want to start executing the cold, you should use the run. But in here in my new you can simply hit on a fresh article. Keyshawn, then. Or you can also press on a great shot. Don't indicate the court. Let's see. Let's turn the corner. Let me take a condom. But no, you can see Hello. Appearing here. So this is how you can start writing your basic courts? 4. Record macro tool: Hello. Welcome back in this video, Let's see how they called Micro using the corn micro option present under developer tha it is only here. So instead of going toe visual basic editor on writing our court there, we can use this recall micro toe. So once you click on this record, macro tool, whatever task you do on Excell whether it iss typing of value or formatting it our copy pasting the cells, anything it may be and gets the college. So before that, you should know two things under record micro option oneness by using related reference more or another one is absolute the next one by before it uses absolute method to record of Michael. So let's see with an example for that, let's click on the corn Michael on in this dialog box, we have toe give you all the information. So it is asking for my crony. You have to give descriptive name for this. I'm giving best one. Andi. Shortcut key. You can kill Sharkey. Anything other than accept before short cookie like suppose I'll be giving chocolate dias de on next gun store Micro. In this option, you have to select this world So whenever you open this one book, you'll be having your micro to none and then give description. Toe this. Since I'll be showing how toe a line next, I'll be writing text alignment and then click. OK, so one thing. You should remember this shark turkey from here from your test one. My crony. So click OK then you can see Micro's already recording. So let's right values in this from Sell Avon. I'm going to write few values, some random letters just to show you the example. So I have written said from Self they want to sell a four. I've written some value. I've selected this on Ben and go toe hole and then click on center alignment. Now you can see I have aligned Tow center on Go back to Della Pieta Clip Gonna stop it so you can see No Now I have manually entered, you know, minor league under I do think that means our money manually gone toe home and then selector Center alignment. So now let's delete all this from here. The content. Let's see if my macro is working so we'll be showing this in absolute more click of a shortcut key that IHS control blest delight. Now you can see on macros appearing here. That means scored is working. No, let's see how to use micro in the little reference more. The only difference is between absolute under loaded. If your relative reference more well, according macro, it will be easier to repeat the micro anywhere on the ship. If you use absolute more, your micro will be recorded only the selected wills. That means only here it will not appear anywhere under eccentric. So for that you can use punitive reference to rip it to use the micro anywhere on the ship , simply contemplative reference and start recording gold again. It is asking for my crony best on short cut key. This time I'll give control bliss and and then store micro in this world book again on Give descriptive name. This time I'll be using the same alignment again. Excellent. Then click. OK, No, let me light. Values in had to so have right Britain values from which to yourself pitch seven. So on Goto home have selected the cells and then going toe home and then don't center line again. So now it is a lyinto center go back to the attack. Click on Stop recording now if you saw I've clicked on using the reference And then on I clicked on the corn macro told I called this one. So now let's see. Let me delete all this clear content. Let's see, I'll place my Carson anywhere on the shoot on. Let's see my micro build runner. So for that, I'm going to you. Shortcut key control. Now you can observe this macro has given the value All right, you It's like rice. You can manipulate the value anywhere you want. Conceit. This is so flexible. Matter value User Micro. When you record a micro using they called Micro Tool using the letter of reference will be more flexible than absolute more. 5. VBA Objects, Properties and Methods: Hello. Welcome back in this video. Let's discuss about maybe objects. So terry parties small, important as we go further. So in maybe, have programming for writing courts. There are few important objects that will be dealing with. They're called application objects workbook objects, worksheet objects on range, objects. So each of this object here has its own set of features, which are quite properties and also its own. Use a square metres. For example. If you take workbook object, it has various methods like opening closing on. Also, we can clear charts and tables in it like this. We can observe how each object has its own characteristics. Once you have understood where these objects and matters, we should also learn which object comes first, right? This is quite baby object hierarchy reaching We got reference much more easier. But this let me give you an example. I've already created model car range object and also a procedure. Some procedure. Now let me give you an example off Bangor TV for me, TV will be in object. You're right, Devi dark because objects are separated by dots in V B. After that, you're ascending property to it. Once you know, baby, you want to know more information about like suppose you want to know how big that TV's I Seiss no size will be your property. So you haven't object on also property. Once you know the size, you're going to give value to it because you want to ascend some value. Do it right. Suppose I want to give for the change right In double quotes fortify inch those, of course again. So this is how you're sending value to your object. That means there are selling property to our object. Now we have a same property. Let's see how to ascend method to it. Now you know you're buying a TV buying a scholar matter so tv dot bite now you haven't object. Announcer method. This is how each object in every way also has its own properties on with hers. So let's see example, it may be environment, so I'll relate these two lines. I Now I'll show you how to add value toe actives it. So right now, if I minimize this my Carson incident itto cell right, this will call This is car less active. Go back to develop attack on Khan visual basics. let me diet value. Do this. Excuse it. So for that, you should Oh, Celinda, uh, object Frustrate Object will be active self dot You're ascending, valued night value. And once you have value, you're supposed to rate what value you want to give in that cell in double quotes, right? And right as exit close the double cords it entered. No, if I enter, I mean, if I run the stool, I'll get this Excel value in this actor. Sell it. Well, let me done this. So let's see. You can observe now in cell 80 we are getting the value it exit. So now again, I want to change the phone name. I'll minimize this. If you go back to home, You're so right now. Foreign dissected Caliber, right? Let me change too If you go back to our develop attack I mean, maybe it no select active cell again. That means your object should always be post dark. And then you have to change point foreign deserve property. Select one conformed. And then again, dark born has radius our values to it. Right? So you can select values one among this select former name right now. select name and then when stool this for name has various names is under it, right. You have to give the name to in double quotes and sell it as a lighted eyes. Ariel NYT area in double course. Close off courts again. Then once I pressed up run button Or you can press if hr cookie Because there are many people sell a multiple cold Seeley to see step by step execution you can press every chart could keep So let me press f it now once I hit a fade Now first line is being executing I'm depressed again. You could get executed on now. It isn't second line, so if I press f eight Okay, let's will get executed on phone. Name will be changed. Uh, area now it isn't caliber if you himself here. So let me press f eight again. If you see for name has changed or area This has been changed properties for your object. So let's see methods Now you have seen nobody's busy matters? No. So let me show how to add sheets. Okay, Right now I have five sheets here. Right? And Michelle how to add another ship Because to give you an example off method on enjoying how to actually to. So for that right object name first. That is work shoots dot on you have to select method. So right now I want to add ship. So I'm selecting on double click on it and you can see this is a court toe our worksheets. So let's press on a chart again and see step by step execution President if he if it again again Until you reach to you current court. No, If you observe shoot six will be at it now There are only five ships, right? Once you click this fade button your god willing get executed. No, you can see she had six is being added on it doesn't have any name because I did not give any name here. Just randomly have given worksheets, not add code. So this is how you can change, uh, properties and matters to your objects like this. There is another thing Quite range property which will see a next video 6. Range Object: Hello. Welcome back in this video. Let's see how to do self referencing using range object One of the important aspect in BB Issel referencing using range objects you can never do but cooler worksheet workbook using range object it refers to cells are range ourselves like a set. It can be under rule column or any selection of cell with indifference to a specific work. Shit. So let's see with an example. Firstly, I've created model called self referencing and then have created procedure some procedural un given the name Acela Princey macaroni. So let's see, in previous we do, we have already seen how to refer to a cell using at pixel right in. That means you can add value toe the cells whenever your cousin waas in. This we do. Let's see if you want to add value to practical assert. Suppose if you want to give value to be, want to be fired, your court will be range. That means your object will be forced. Then open the brackets, then yourself referencing that this your range in double quotes Be one don't the fight right? Then close the back it again, then equals to sign. Then you can ascend value in this sense. Suppose I'm ascending Hello in double course again because this is a text. So this is how you can add value Toe selected range. Okay, so let me click on run, but and see Yeah, you can see Hello. Appearing here on Let's see again. Another example. Suppose if I want to select this particular cell. That s C eight. If you can see here, go back to be it. This is only single cell right night range Again Open the brackets. Selector cell That s C eight in double courts. Close the bracket and see. Suppose I want to give value. Let me done this. I can place my cars that anywhere you could give my value in like about I have given make c eight right a condom. You can see value appearing in cell C. Likewise, there is another metal also to refer to a cell You. So let's see for this Usual Right says in brackets you can see it is asking toe enter no index and column in. Next number in this matter will use Hello. I called number. Suppose I want value A This said notice. No one I'm calling for right and don't rule one comma foot and then close the back it again , then equals to sign. I saying value to its supposed 50 I'm giving for Let's see un run this you can see in Selby one, it has given us value. So this is also another mature toe. Refer to us as we have said. Still no active so reference matter, range reference matter on also sells using sell index. Right, So this is the various matter how you can refer to us but but most commonly will be using arrange in most of the lectures on it is widely used object. So this cell referencing metal is only owes for few things like for low on if look so that's all for this video. 7. Working on Worksheets: Hello. Welcome back in this video. Let's see how to manipulate multiple sheets so that is inserting, deleting or the naming it basically. So if you want to work on workbook, it's essential to know how to refer toa worksheet. So let's see. Firstly, we have toe inside a model goto insert menu click on model. So I'm going to change the model name on the properties. Me, I'm changing into worksheets on a school one entered No. Firstly, I need to write procedure some procedure space by Cooney Worksheet under school one it So no, Let me show you how toe no around different sheets. Right now it's in cheat one she twenties activated. I want to activate sheet for so let's see writing Oh, cool silage. What right worksheets That is your objects first on their in brackets I want to go to cheat for right cheat for in double goods, then dot Active it You want to activate Chief? All right, so activate. What do you have to mention there? So when a Priestess run bottom, you can see it will come toe selection will be in ST for Let's see now you fumes Oh, from sheet once it has selected cheap for So there is a slight change in the same core. Let me show you. Instead of book sheets, you can even diet sheets in brackets again. I want to selection three. This time she three not active. It rest everything for loser. See? No restaurant button. Yeah, you can see she three is activated. So both of these courts you can use when? When you want toe active movement. Three sheets. So basically, this one is more descriptive. So always try to use worksheets instead. Up shape straight. So next comes I'll show you how toe select multiple she So for that you need to mention a world court selection incident. Activate. You have to write Celic. So let me show you. I'd walk sheets back. It's I want to select both sheet one. She do this time. Now I cheat one post dark Solich. As I said. Then comes She do right? Worksheets she do. Don't sell it. So by this we can select multiple sheets. So before dark there is something extra they you have toe on. After Celic, you have to add work falls so that you can select multiple shit So if I just execute selection with the these two metals, one after the other, there are chances that it will be Cheers Will really placed right. So on select sheet one first that this I'll press on every shortcut key on get their cars are here so that I don't want said by step execution. I want to execute only this course. So I bought the cars are here. I'll press Fuhr Turkey No. And simply go on, she toe. So if I have the world falls, it will tell Excel nor to replace our current selection. So that's the reason you have to write falls after you sell it? No. When? Empress if. Eight. If you absorb No, she had wanted Cheeto. Both gets Celica it. So this is how you can select multiple sheets Now that we have seen how to select multiple sheets. Let's see How so? The name the sheets, light worksheets then in brackets. Whichever she if you want to be named the name. So I suppose I want to rename she for dot name equals to the name. Really? Me. I mean, you want to enter the value here. I'm entering thus deep in double quotes because this is a text. So when I execute this, you can see you shoot for change its name to big This defeat if you absorb it is changed. Now let me show you card Really sheets. Sidewalk sheets again. Then comes your sheet which allows you to want to relive mention that cheating. So I want to actually delete this date sheet. So light did it Dark village So in earlier one a look Or you had used name method Right now you're using the lead, Makoto, believe she So let me London called best If a chocolate get so it is giving some pop up saying that I may exist in shape selected for delusion toe permanently delete the sheet press village. So I'm deleting my the shed because I don't have any useful that other. So I just want to believe that she president Bill it You can see she gets deleted. So this is how you can manage plate with sheets. So one would they use whenever you want to let photo sheets? I've used sheet for that ISS This name matter, right? There's another matter also to refer to ship. We go toe sheet. One on under properties. You can see this cold. Your but she had one cheat. This is up court you can use to never instruct Writing the name. The devil is your You can use this. Coordinate that ISS, which means you can refer this in brackets. So this is also one of the metodos for two sheets and it is more reliable one. So that's all for this video. 8. Working on Workbooks: Hello. Welcome back. So in this video, let's see how whenever the workbook and also switch between different world books on finally how to say you're micro in number What? So let's start. So for this, let's writer more. Let's create the morning I'm the naming model Name toe one books underscore one Did Kendall on right or suppress it? So name of the morning school one on a school one hit Enter Empress So when you want toe switch between What books? No. For this exercise I've created another one book Good book to this is my earlier book This is my new one book You can see three shoots in it So right now if you say it's in book to write that this I don't reduce the size can see more clearly So right now currently I mean book I want to goto book one. So for that you have to write court logbooks brackets You have to mention your world book name Whichever book you want to left I want to go to book one So I'm writing book one night . Don't you have to mention this extinction name ones when you want to If a Rip X. It's excellent, Essam, right? Because it has micro courts and it so extinction name is different for this. Then comes active it. So once I pressed the wrong button, you can see the changes. Now it's in book two. It'll go to book one. Let's see, you can, um so, uh, one is activated. So, like this, there is another matter Toe, activate this, uh, world books. So let's see, That is by using index method in next number. Right? What books? Again? So first book which I created will be one index number one second book will be indexed number two. No, I want oh, this book to so I lose index number instruct World book name. I do on dot activate, get So it will go back to book to. Now, if you're under short cookie can see it's going back toe. It's activated now. So then comes how to refer toa that particular workbook on. Also, if you want to open or close that particular So let's either. Hey, don't no book doors activated right now. I want to close book toe. I want only book one visible. So for that. So now you're active a book is booked then if you want to close it, right. Active workbook Dark. Close. So this is how you can close this active work? One more thing while closing it will ask us whether if you want to say the changes are not for that, you should use and extinction to that court Coy at the World Book Start close space. If you want to save changes, you should give true. Okay, you can see here. So there's out. You can, uh, closer that particular actor. So let's see. My pressing afraid shot can change a state undoing this so thank you cannot really drink sheets. This particular court will work right now Let me press afraid, Shark. You can see if I execute again. That means if I press another time, can see the particular she disclosed and also saved. But now let's see how to say this matter Annabelle She So if you have my gross there is like change when you want to say this one book So goto file selects Eva's on Then select the location Very want to say this workbook then cancer. Finally I'm keeping a notebook one. Then one thing you should observe its save s type. Here. You should select Excel Micro Anybody, Bob, when you're having micro's in your workbook orders, usually it will be acceptable book. Right? Say, this type will be excellent. Since we have micros, who should select this option? Second option, then click on save. So this is how you can save your macro she 9. VBA Basic Variables: Hello. Welcome back in this. We do. Let's see how to write variables and also how to declare statement. Using implicit on explicit statements on also will cover others, which occurred by using variables So variables are basically used in all computer programming. I'm even in BB will be using them. They can store value in our computer memory so that whenever we going them, we can use those value. Execute other courts. So one thing you should remember is each way will contents a unique name on it has few set off rules like it should not contain spaces on numeric value should not be entered in the beginning. Off your variable names you should not enter dark your variable wing. So let's discuss with basic example for that I didn't feel things here in my excel itude car names. I'm unit soared. So let's see in visual Basic Editores. So I've created model name here as very was one. I'm subroutine. So let me show you how toe our value in cell Ayten. And also how do how we get a message boxy That value is added in Aden, so let's see. For that I'll start with the range declaring, I mean, I'm writing us. Object first in brackets. Sorry. Self referencing that is eaten. I want value in eight and write about value equals two. I want to add new Carney. You're suppose I want to add Nissan hit. Enter. Next thing I want is I want a message box, right Message box space and in double course, right. No message. The sun he's added. So this will remind message. So when I hit a fade button, I get this my search box value in myself eight. And so let's see. So my court is getting executed now and got executed. I I goto my city boxing me Sonny's added So suppose whatever I want to change the car named oh so Zucchino every night. So Sochi, right, Suzuki? Now I want to change in two places. One is in range, code on also in message box Score, right. So I don't do electric. So incident this. What if I have something called variable which will store my car name there. So for that you should light available in at the beginning of your some. So once you're right. Just so you can, uh, specify your valuable thing I'm writing as new car. This will be my variable in which would store the value in it equals two. Give value for your very building in double quotes Supposed Tsuzuki, right? I want o get Suzuki whenever I have a new car in my accord. It didn't give me Suzuki value in it. So I distort storing the values. Who's looking it right then in range instead of Suzuki. You can write new car because new car equals stood Suzuki, right? It gives his ok here. I mean message box. You should up. So I'm removing Missan from here. I'm keeping the score as this before that I'm writing you got This is my variable space. Maddie, I'm, uh this This is because this will on captain 82 sentences because there are two things here . One is the comment on also the very building. Once I hit John Wooden, it will execute. Let's see, I'll press if it you can observe Suzuki's added here in 18 million ghetto message box words . Sorry. Yeah, you can see message box here. So Sochi is added. So like this, you can I send values using available. So this is quite implicit declaration. So one disadvantage over here is when will you declare variables using implicit metta? There are chances off ever so, Lexi and give new variable e you call one equal stoop. Mr Might give Fidelity. I am storing Ferrari in new car one variable, then right range. You're, uh, difference. But it's this time I'll give cell 11 dot Value equals students. You can see everything is same as foot the about codes, right? One thing I'm doing here is instead of new Calvin What if I mistakenly enter as in, uh, Ness Car one. So there is spelling mistake if you also instead of new car I have used s right here less car one. So let me down the currency If it will give the value in a flamin Said I should get value if Hillary elements. All right, so let me executed being discussed young. So let me frequent afraid again. So you see, even after executing the court, I don't see any core any value appearing in 11 cell, right? This is because that is spelling mistake Here you can see here. So the available which have declared its new car But what I'm using this. This card One, right. So it is something not declared so These are not errors when you use implicit way all the closure, we can avoid this by using explosive declaration. So let's see late for that We should late explicit. I don't beginning off your son right in the I'm. As soon as you opened the model, you should write option explicit. So no. What do what you have to declare should be declared using explicit way that IHS using are very bulky. Oy, dim. That means dimension. So it's always better to use this explicit way off declaration which we can ah white few of the others. So let me night delete this some procedure. Why don't you want so maybe of this one? So let me show you an example Here. So in explicit declaration, introduce what do declare your state's bill space on your very building? That is New Cartwright. I'm keeping it and keeping the over valuable new car peace. What do wonders store in new car? That is what you want to store in that area. But I'm writing s drink. String means in it. Next will be Leffert West. Drink him baby. So selects tree, then end us. No. Once you declare you are variable, you should assign value to your baby. But not just for your new car. You should assign value request to sign. I mean bracket on in double courts. Right. So this time and use I already ate. So again I'll internal Lynch range in a 13 cell No, a 13. And see if this in book value questo, you got right. So what I've done is I have declared statement and also I've given value to my valuables. I have ridden range. So let's see. Undress If a child could If you see sell a 13 I got when you already ate, right? Suppose what if I do something instead of a car? You so instead of new car have given new cameras made available memory. So let's see in this way, execute or not, Firstly, I'll remove valley from cell 8 30 so that you can also this school will give us that all Indian ever here right now getting the bob because once I'm less afraid, it says very well not define on it also it's highlighting me. What is that issue here? You can see new camp This highlighting this variable It's not defined. So whenever you use option explicit at the beginning off your sub blurting you can a white beyond the Mystics. So, yeah, this is how you can use your coats variables. 10. VBA Basic Data Type Variables: Hello. Welcome back in this video, let's discuss about revered Dr Types. So basically, we are talking about stories, types in computer memory. That means what each very welcome storing it. So in previous example, that is in this example, we have already seen one kind of data that is, drink right. We have assigned or variable and also assign doctor time to it. So in this way, doing that, see how we can write mine, depend acted. It's on how we can use multiple data types. Right? For that, it's better if you understand what kind of data types are available in exit. So go to hell. Click on Microsoft Basic help. And then you can search Dr Tight here. Hey, Tender. Now you can see that that I'd somebody press on this. Now if you absorb this is a somebody valuable find that the types told its size and also lynch. So there are few limitations when using this kind of doctor type we have already used drink Right now in this example, let me minimize this. So in this example, I want value in cell, a three time 800 meter ting. So both the places I want. Really? I want to ascend variable and also data types to it. So I'll be using talk and all data types. One is string on another oneness, uh, in Egypt because indeed a holes numbers on string holes next. So you should know how to differentiate between your doctor types. So let me right, let me declare. But using them right and then I'm declaring my variable. I've already declares one of the baby, but you're but new car declared now unit sort right? And then like us. Then it will come us indigent because my you need sold value is over 800. What if I'd used in digital Bip inside off indigent? I cannot use bite because it looked to me and and because my unit soil and you will be 800 right? If you see, for this bite type the rangers only upto 55 you can enter only upto 55 number, after which it will tell you an error. So that's Ellington. I'm using indigent. Wait. Hold rituals higher value than bite right. It holds up to 7 32,067 So I'll minimize this and go back to us court So now I have declared my available on. Next thing is after assign value to my variable. Right? Maybe Molinas unit soared. I've made a mistake here. Saudi. So you're need sword. I saying value toe this. That means I'm ascending when you do. Units sold 800. Then comes my lynch. Right? Right, range back. It's sell referencing the 30 Dr Value sequenced You need sort. So this is how you can I say my people, doctor tights to your valuables on also declared them. So let's see how to execute on. We'll get their output or not. Let's see, miss F eight. Um, zone a touching should get all the aid. And also if you press again and it value 800 So this is how you can declare multiple Dr Types, right? So I've said using this data types, it will throw errors also sometimes, right? That is because instead of using, only there is a limitation for every night that abuse, right? If you cross the limitation, it will tow us. So let's see. Instead of 800 I'll write you seen in digital. It holds value of 7 32,067 right? What if I 32,000 7 60 egg. One number More than no sexually right. So likely. Learn on Absorbed. It would give me in value. So I'll delete this to again. Executive board ones go back to visual basic. So let's see, it is giving an enter coy overflow. That is because I've used exceeded the value. So this is all you can the photo this help button? Sometimes. Night. So this is a basic off data types, uh, explicitly declaration. 11. VBA Range Object Variables: Hello. Welcome back In this video we'll be discussing about object valuables. There are many objects in maybe right like range object worksheet, workbook on. Much more so in this particular widow will be discussing about range object. That means using cells on, see how to declare them. And also I'll show you the basics in Mexico's from it I'm how did ever toe particular set off cells? That is strange. So let's say with a simple example before that, let me tell you the difference between using range, variable and also data type. Very so In previous video, we have used that attack valuables right? If you see in major answering were data types I've used toe declared them. So once I declared that the types I am saying value to my available using equal to sign. So when I use range object instead of just declaring value using variable name, I should use a Your guy said so let me show you. So for that I have created more. You're going to be injury labels. I have used explicit statement to declare it, and also I've created some process. So let me show you a simple example. Toe have values in said it's quite a 13 to 21 values in the cells. Andi, I want to enter values through message box. So let me show you how to do that. Plus, you declare using them statement on your variable him up the space that this I want. Oh, give Robin names. Flower means in this sense then right as on your lynch. So I'm using ranger object here. Then use the word said, Place your variable name. So whenever you use range, you have to use our key were set, then stole the range here. So range off cells that is from a 13 toe a 20. I want to store this cells in trouble. Name variable. Then you can use this flour me in your court instead of typing this big Lindy court. Right? You can just I travel name. So this will give the cord, uh, range. This is hunting the range off cells dot Value equals two. I want the message box a enter flubber mean so input box Black. It's right. Flowers in the flower names. So whenever I execute this, you can enter your lover name in the my search box. So let me execute this. My pressing every jacket. So if you observe I got up my search box. You're right on right. A flower named Rose and hit Enter. So if you see in selective set off range I got the value that means throws us Right? So this is how you can use your range Object? Oh, enter values with slight difference Using said keyword. Right. So let me show you another example on believe this and show you how to be a photo. This particular cells That is a dato a nine on jeans. A front off the sense right. Then declare statement using them. Notice dimension Then you're very believe that Iscar names as range because it is a set off cells. Right then, like the word said car names that is available then includes to sign your range reference That is a photo if nine Sorry, then comes You're good night that it's card news dot value So you know, as I wanted jeans a form and show you how to change the phone Bold. I'm using the function quite bored then equals to sign. You want to make it boy or not? That's true offers a light truth. Now if I execute the score will see that change is happening. The observed the phone distance to board So in this view, a strange it dodo a nine right. And then chains up Fundo boy. So in my previous video, if you're noticed, I've shown you the examples off how to refer to rangers Have to set the phone to boil on Exeter. So if you see those, we do not understand this particular example And then what if we want toe change value off just one particular cell that is only a nine. So I want Oh, change the phone color for this solely color equals two rgb Let so this is a cold you have to use whenever you want to change the color Arjun movements set off combination trickles that is red, green, blue on your color name So let me execute if eight So if you absorb no for sell a nine the color is said to lead So this is how you can manage plate your cells using range variables Night 12. Using WithStatement: Hello. Welcome back in this video. Let's discuss about good statement. So whenever I'm writing range, gored multiple times, I can reduce the usage off that range court. I think, though court again and again by using a with statement. So let's see how it is. Suppose I want to change formed off this particular range That is a Toto 889 I want to change the phone size, fun color and also I want to make the phone toe bull So I'm using three different properties. You're right. So by using that statement, I can just reduce the time taken to change up cooperatives when I use the with statement. So let me show you how to do that. Got a visual? Basic territory have created the morning on little some procedure Here, let me light Believe on show you different properties My self referencing a tow a nine dark point not size equal Stop 14. I want to change color off the point it Toto 89 don't phone dot Color equals two RGB is of course you have to use. Then I'll give Brown. So what? I've done this every don't know different properties, everything the range Unseld A printing twice in this gold, right? So instead of writing again and again, I can live with the usage of this by writing a statement. Goings with statement in the beginning, off the court night with on bridal repetitive word which which is repetitive every time your range Unseld referencing is repetitive for list of the courtside. So like that, I like that part here after your picked statement and then you can Delinda below core breach gun days late Dulic range pot on your corn is only starting with dot On continues with property straight. So like this you can start your court of it dot on then I want to change formed make it don't metallic equals two. So this is a shorter matter. We check in yours by using with statement Andi have to end a statement with end end with so one thing you should observe it. Start with on any quick the end with a statement, Right? So once execute, all these things will get executed. So let me know if it Yeah, you can observe the changes happening toe this sense. Right? So in this example, I was doing my self referencing That is a to do any. What if I didn't know where to India? No forensic, right? If it was kind to me till end how to refer to that kind off? Lynch, let me show you that. So for that in range column, delete this a nine chill and then writer starting off your cell, That is a toe coma. My d'Orleans, nothing's a do again. There was a bracket dot You're starting with a door on ending the AM statement and then in brackets you have to write Excel don't. So, by the score, you can see you're, uh Macron will refer toe hold particular. Call him. So whatever you're writing here will reflect a list of the columns if you write this court . So this is according you have to use to reflect the changes to the rest of the course here . Right. So this would make of it. What must Mindful easier instead of writing the range on drift operating this big called again and again whenever we want to change the property. Right. So this is it 13. Loops using For Loop 1: have welcome back in this video. Let's discuss about rooms. Bathrooms are basically used to protect us. So repetitive task is done by using loops will see most commonly used loops. And maybe that is for in this video. So far, follow is used when we know exact number off reiterations to be done. So let's start basic example awfully pitting message box three times using for. So Goto, BB editor. You're have already cleared for more. Do I'm letting us off procedure. So for that, let's declare available first the X as in danger because I want to repeat the past over three times I'm using Impeach Awards. Then, after declaring I sing Value for X Light four X equals toe, How many times you want to be like the number one? 23 times. Then again died your message box. I'm I saying value. Want everyone to explain your message box, then toe end this for you have to use a word called Next. So these are the steps to be followed for. For once you execute the button. Let's see what happens. Chris Button So you can see once click again twice and then 30 times and guard executed, so you can change them. But you're This is available. You can keep on changing as to how many times you want. We've been the message box. So if you want your message box too Uh, sure. Valuable. So that in So So if you want your message box, show you value of X here. XX close to 123 Right. So even in message, Boss, you, then you're macro weapon. You were fine. My message box one, My search box to message box three. Appearing whenever you because we'll be assigning value of X here in self 100. You get Hossein? Yeah, and they take on, you know, do what we have. You can see one door. I'm city. So if you're so like this, you can assign value for X. One thing which I would like to tell you is if you want, just give them number instead of continue somewhere. If you want. Skim the number three right step. That means Venable. You click the message box so the numbers will be starting from one on. Then if you write Steptoe now what? We jump two steps forward. That means it will come down So let's see how vehicle one button. So picnic you can themselves. It's not giving me door because directly given me number three here. So this is how you use your step indoor option here. So that's all for this week. 14. Loops using For Loop 2: Hello. Welcome by in this video. Let's learn how to ascend value to cells using for loop. So are you using the same example? I'll copy basis set off micro lines. They said there were you on passing. I'm going to do rece gentle micro name here. Both the macro names cannot be seen in this model, so I'll change it. Oh, on one more thing. I'm doing this all changes a number to 10. Andi, I live with this message box here since I want to I well, you know myself. Albie. Use writing cells in black. It ruin Nexon column index number mentioned Your low index and column index number I don't know. Short value from this is even toe Aiden. So I'll be writing one coma. Big cause Elderly wife Dort value. I want to assign value. So right. Value equals two. And so one comma. One minutes. Your phone number and call him number. Right. So my column is saying so, Al, keep it is I'll keep it as one only low keeps on changing If I add the value row keeps on changing one by one. Right will come to first row. Second, no title so that will be a variable here. So a light as X, then? No. If I place my car zero year, I'm Condon Button. Let's see what I have. So we can observe. I'm good values in the sense 10 times, right? Because I've assign value from 1 to 10 here. So I have got the value then. So what did we have some changing value here instead, off just then. What if I write as X treatments are available? So if I like eggs in for salad visual was this one on second cell will be 23 and goes on until then. So let's see and click on John Burton again. Placed me cars are here. Click on run. You can observe Old. No values have changed too. Oh, different numbers, right? That is 1 to 10. Okay, now we can use step function here. That means you can write step here whatever number you want. Toe jump toe. Right it over here. Suppose all right today? No, if I execute or play one, but let's see what will happen. It is not giving me any answer because there are already values in. This is so like me. just in all these values and then go back to my visual basics. Now click on run burden. Let's see what a lap So you can observe since have written Step three. It has kept the numbers in between one toe, three un given me. No, I stopped the Did you see it? So after one, it has given me for seven on 10 because we have little as Step three function here. So this is how you can assign value to your cells? 15. For each loop: Hello. Welcome back in this video. Let's see how we can look over a collection off objects in excel. So you can, um So here there are three cheats here. So this is called collection off worksheets in workbook. Likewise, these cells are also called collection off objects in this world book, that means strange. So let's see how I can get the names off these sheets using for loop here for each look. So for that I have already written a model here and then didn't answer procedure. So first thing we have to do is declare off. Variably. I'm writing Dim. She is my available here as work. She so have the claim. Variable first and then write for each eat. So whenever you're using for loop, you have to write for each She for each eaten this web because you have don't write a fight here. Dark sheets. So you're writing for each eat in this world book dot sheets. This is the storage place where your ch'tis store currently, right? Then you want this name of the sheets? Should be despairing. A message box. So I'm going to write message box space. She dark name your sheet on, then Name. That means you're really but name. Then you have Don't end up for Luke with Ward Corner. Next as usual. So now place me culture. We hear on, then I'll hit. Run. But let's see what will happen. You can see we got cheat. One name here and then click. Ok, she to she three on. Yeah. So this is hope you can list the names of the sheet in for Roach for each loop example. So let me show you one thing here when you click on a feature cookie. If you keep your Carson on name, it is showing currently sheet one. So first time you're learning your of course, So it will be showing she'd want Whenever you have, like multiple sheets, you can have Ah, look at what? Which it is. It is currently running on which each name with it is going to get. So this is how you can himself here make a faint again. Okay, then comes if it again. Now it is going toe she toe if you observe here if eight Okay. Again a fate she three now select a ship three afraid again and click. Ok looks like like a free. It is taking the cars Ato and some. It has come out of the loop here because there are currently only three sheets here. If you observe so it is walking. It is giving the names off all three ships and then it will come out of the loop. So this is all It wouldn't work here. So that's all for this video. 16. If and Else If Statement: Hello. Welcome by this video is all about testing If some condition is true using if statement on also how we can use multiple if statement using else if statement So let's weaken with basic example of showing a student school So we'll see if the result is pastor feeling using if statement condition. I suppose in this example story in school is 55. If the school is more than 30 If I I want the result, we show nice spies here. So that is a condition will be testing here Let's go back to maybe it on right over metro court. Right. If statement condition, use if then comes your range condition you want to use Where is it exactly? Located that IHS Beato, right big toe dot value. If Vito is more than ratify, then rage then Now you have to write your destination off your result When the result has to be that means in cto I want the reason so right value It was two in double court. Whatever reason you want to show this right, it will there. Then you have the ends with and if statement you have to always straight and if you're using if statement. So if we do is greater than 30. If I show me pass, let's stays this condition click on run button so it can observe. Here it has Give me given me the result as past this condition is absolutely walking now. So what if my score is less than terrified? That is my maybe 20 and president of HR Cookie. Yeah. Even after execution off this lines, the score is result is showings the last parts Because I have not mentioned anything other than past condition here. If you see only if it is go to then Ted, If I show me a spots So it's giving me only one kind off result that is passed. So if you want to satisfy both the condition, you should use defense condition on for doing the protective task we should use for. So let's see an example which will satisfy both the conditions. Let me remove this statements from here on right on you statement for and statement. So now if you see, have lead in the example off student names on scores upto sound students here let's see, the reserves are parcel free using else if statement. We have more than one student. I'm more than one condition here that this it has to show a spots on. Also it's school is listen, terrifying, virtuous, say so. Let's see. Firstly have to declare variable here Dim ex us in digital because it is a number they after declaring valuable I sang value to that variable night for X sequence tools Don't don't Sorry because the numbers are starting from Dodo Some in here. But this permissions we have to sure still isn't from they don't go be something so I'm writing. Don't do something here then, right? The condition here If says ex common do not you is greater than certified then sorry equals to 31st Berry Show me says X comma three documents their destination dot value equals two in double cores us This right as says This is also in destination. Now we are mentioning Harris says ex coma three door when you show us fake. So if you're absolved Exco motto that means in second column Now this course if it is more than certify you me past a card call ISS orders give me faith and then right in this then we have written in this tube Closer if statement on then we have the right next to run the look for the next statement. When I give, my cars are here on then president Fate. Let's see what happens so you can observe if it is stratify all equal. Do it is giving me us so which is absolutely night presently. Feet again. This is also pass. This is fail because it is less than 35. Next is also failed free on bus. You can see I got the results here by just clicking on a French or Turkey. So now one no condition like me out for this. Let me out of color for Parson fame. Every spots I want green So But if it is saying I want color Toby read. So let's see how that says Ex Coma three. So far. X commentary. I want inferior. Not color equals two three b. Good, really good. So if this powers I want green on, if it is, Fay says, ex comic etc. Dog and devier North Carl R equals two. We be so it is fail like color. Less so let's see by pressing afraid chocolate place my cars in here and then click on a feed. So if you himself 1st 1 is green because it is a pass. Next one is also going harmonies, right? So, yeah, this is how you can apply your conditions using if unveils if statements