Excel VBA Tutorial for BEGINNERS | Excel Macro

greetings everyone if you are an excel user and always wondered whether can i learn excel vba macros the way everybody has been learning the coding well if you follow my approach you can in this video tutorial i'll talk about how can you learn macros what is a sequence of steps i'll start with a quick brush up on how to record macros then i'll show you how without knowing coding you can go to google find out relatable codes which already made copy and paste that in the right window and make your automation easier along the way we learn the theory as well so i'll talk about object property and methods next i'll also talk about cell and range object which is very important concept in excel vba macros so my job is to ensure if somebody wishes to learn excel vba macros i should make the journey easier so why wait let's start now people ask me why should they spend time and effort in learning something which will take some time well let me show you some examples and this these examples are the ones which are going to help you streamline a lot of tasks in future if you are working with excel or any data which is very large enough example number one assume that you're working with different worksheets it could be income statement balance sheet fund flow statement cash flow statement projections or you might be working with different locational worksheets in this case as you can see at the bottom of my screen australia india south africa sri lanka us uk china workings one two and three well if you have to send this to somebody it would be preferred that you create an index of sheet names which if clicked upon should take you to the other worksheets as relevant now if you had done this on your own you would have had to copy this particular worksheet name paste it into one of the cells and then right click and then say okay let me go to hyperlink and then you'd have to tell excel that look i wish to go to a place in this document and then which document which sheet okay this is the one the one which says australia right now once i click on australia only then it takes me to the sheet called australia well can you imagine how much time would it take if you had to do this for almost a dozen worksheets and not just that in future if any change happens in the structure of the workbook the name of the worksheets you would have to re-update this well with macros only a click of a button and if the name changes let's say i'm just making the australia as aus the short form let me click on the button again you notice how quickly it does your task well this was just one example let me show you one more here before me i have another workbook whereby sheet one two three four four worksheets have different data the sequence of data are same that is the client name country city location of the particular office and the amount now these four sheets contain different names and numbers imagine if you had a dozen worksheets with data spanning over hundreds of rows and you had to combine them in one single sheet what would you have done well you would have copied this go to sheet1 paste it beneath again go to sheet3 copy this all right i can see that and paste it below why if you have to do this a number of times in a month in a week this is unnecessarily spending and wasting time it is not going to add any value to your knowledge base well with macros let's see how fast it can be done so i go to developer tab macros and i click on this macro merge sheets and i simply run it look something happened in the background let me see there is a new worksheet that got created and that's the name combined well this has combined all the data in what one or two seconds at max so this is what macros can do for you and before i close this video let me show you one more example something which is based on user defined function or a formula quite often people ask me that the invoice amount that they arrive at in a certain bill amount i want to convert the number into text something that says rupees two hundred thousand fifty point fifty percent right so i have this user defined function which i'll be talking about in the course of this entire program number two words if i click on any one number and i close the bracket enter notice rupees 52 600 only and uh if i had a pair say point two aha and pesos now how cool is that so you learn all this and much more in the videos to come and i would want you to believe me and just stay all along throughout the end of the course and trust me this is going to be a lifelong investment that you will cherish till end of your life now we learn how to activate the developer tab on your excel screens ribbon you will notice that while working with excel i have been frequently been using the developer tab well most of you who would not have worked with excel macros earlier would not find this on your screen so i'll show you how to activate this in the version 2007 and the versions thereafter so first in 2007 let me activate one window of 2007 version of excel so how do i know it's 2007 well the moment you see this office button the one in circle in shape that means it is 2007. so if you have the circle button on your screen that means you're using 2007 and to activate the developer tab you must click on the circle button once down below you will notice excel options i would want you to click on that and within that the option of popular once you go inside popular you will notice a third option from the top which says show developer tab in the ribbon well once i click on it and i press ok it will show me the developer tab in 2010 13 and 16 version of excel let's see how to activate this we go to the file menu on the top left down below you find options i click on options within options you will not find the popular tab as you had found this in 2007 version of excel well this time you'll have to go to customize ribbon tab well the logic is this entire section of excel top hand screen is called ribbon and you wish to add one tab on it hence you are going to tell customize ribbon within customize ribbon you skip this one dialog box and go to the next one you notice a list of all the tabs well in my screen the developer check box is ticked on in your case you will have to take it on as well and once you press ok you will notice on the top right corner of the ribbon you will find developer tab so this was all about developer tab before we start working with macros let's just go through some of the very basic important information because that will help us save our work correctly and execute our work correctly now what i notice on my screen the one of the files which contains macro the extension of the file name happens to be xlsm not the xlsx that you have been working with regularly on a day-to-day basis why if you happen to record or write a code in your file and wish to save this for future use you must tell excel that look this has to be a macro enabled workbook and you must save it accordingly macros if saved in the xlsx format will not reactivate or re-execute or get saved for future after you have closed the files once so if ever you are working with a macro piece ensure that you're saving this in xlsm it's not that difficult why because you can go to file save as and when you go to save as now let's say i wish to save this on one of my modules let me go to one of the modules and you will notice from the drop down it gives you variety of options one of which says macro enabled workbook now some of you may not get this extension listed out here because that's one of the folder options settings from the pc which have activated so it's quite useful to have those setting activated and i'll show you that so i go to the window option window x is a shortcut which i'm pressing it allows me to go to control panel either ways you might access control panel from your pc once you activate control panel one of the options you might get after you check on the small icon or the large icon is folder option let me go to folder option yep as i press the letter f it takes me to folder options i go to folder options and then i go to the view tab within view tab there is one setting probably seventh or eighth uh yeah which says hide extensions for known file types now by default it is switched on if it is switched on you will not be able to find the extension so easily listed out on the desktop or in the saving panel let me press ok and i cross this out let me now save this on the same folder let me see what do i get notice i don't get the extension of the file i just get the macro enabled workbook well i would prefer to have that extension shown to me hence i'm going back to the control panel setting go to the folder options and i make sure that i go to view tab and check this box off which says hide extensions for known file type i apply this and i press ok so henceforth we'll save all our work in the xlsm or macro enabled workbook now this time i'm going to talk about one of the security settings which is very important if you are working with macro for example if i'm going to open one of the xlsm file the macro enabled file it has certainly one of the macros which we saw in one of the earlier videos as i open this notice i get a security warning which says macros have been disabled and what would you like to do from here well i'm going to say i'm going to enable the content because if i don't it will not allow me to run the macros and work with macros now majority of you would get this setting and that setting is residing in one of the developer tab security settings let me show you where i go to developer i go to macro security button on the left hand side i click on it and once i click on it i get four options these four options are the same in all the versions of excel 2007 1013 and so on what it says currently it says disable all macros with notification which means if any user opens the files which has a macro it will by default disable the working but along with it it will give a notification which allows the user to activate that and this is exactly what we saw few seconds back well since i know that i'm going to work with macros very very often i do check this setting on enable all macros although not recommended why because you might get some code from other users or the internet and by default if it is run on pc through the excel macros it might damage your pc why because somebody might just code some erroneous code or maybe have some malified intentions now of course i'll be being very careful while i'm writing macro and that is the reason for our purposes i am enabling all macro and pressing ok so that next time i open the file i will not get that particular warning see i reopen the file no warnings so that was one of the settings of security while working with macros so by default either you choose the second one which says disable all macro with notification or you may say enable all macros that will not ask you any question regarding running of macros now that we have seen some of the important settings of security and how to activate the developer tabs in various versions of excel let me just quickly help you walk through what's inside the developer tab well please do not get overwhelmed by the different buttons that are placed on this panel as of now to begin with we're going to only focus on this one section of developer tab which says code visual basic macro record macro macro security and and we will also focus on only one subsection of this insert button which will allow me to draw a button which if clicked upon will activate certain macro so i can associate that button with the macro so that next time if i click on that particular button that macro is run right so as of now please ignore all the tabs and options inside the developer tab we'll be discussing that later as in when the relevance is felt now let me pick up one of the most important questions before we proceed that is how should you start learning this macro well if you go by the traditional approach of chapter one two three and four going by a certain textbook you will fall out midway why imagine if i just continue to discuss theory theory theory theory for the next 30 60 odd minutes you would get bored because it doesn't lead you to an actionable result and that is why i see people dropping midway while starting to learn with macros this is not what i'm going to let happen i will adopt an approach which is much more faster in learning because i don't go by the theory theory theory and then practical practical practical i go with practical practical practical theory theory theory and so on so forth so let's see how this path leads us to the right right direction well this time let me show you if i open one of the xlsm file how do i get to see the code i may not have written the code but i downloaded the file from the internet and i wish to see the code i go to developer and i go to the second button which is called macros so you notice on my screen there's a shortcut key for that as well so eventually as we get more comfortable i will be using the shortcut key alt f8 to activate that once i click on this notice i get one name and that's called merge sheets now it's supposedly a macro i don't wish to run this right now but i wish to edit this although i may not edit but at least i'll be taken to a panel where the codes are mentioned first things first don't even start reading the codes no it will give you a headache what i want you to read is just the first line and the last line of the code temporarily i'm just cutting the entire thing out notice what on my screen you see sub name of the macro followed by a pair of parentheses and the end sub well in my discussion i'll be referring this as head and tail of a macro whenever you scroll through any book or internet or a blog and you come across this pair sub and n sub please assume that it is a macro code that is there now there are two types of macro in general one which performs a certain action action could be that it deletes all the worksheets or it creates a link of all the worksheet names or it could be a user defined function now what is a user defined function if you apply a formula called vlookup this is a system defined function whereby the system already has the codes written for vlookup and accepting various arguments sometimes a user may wish to create his own function which is not there in excel and that is made to perform certain calculations so that is called a user defined function in short also called udf if you google around udf you'll find a lot of text on that now how does that udf look like i'll go to macros again one thing is for sure it doesn't get listed in the list of macro button i'll have to go to edit and notice below this first macro there is a line why is there a line because you said ends up that's the end of acro tail of a macro and after that there are some comments marked in green and the starting point is function name of the function and then after certain line items just ignore these intermittent text for now notice there's an end function so just like sub and n sub n sub in general which is the head and tail of a macro for udf for user defined function it is a function and thereafter end function now why am i discussing this right now why because i want from here on every macro that we look at we must classify them in the normal action taking macro and user defined function that's the head and the tail of a macro now in this session i'll show you how to associate a macro with the button a button which can be clicked upon and the entire series of macro can be run based on that so we had seen in one of the earlier videos there is a macro sample macros codes of which i have not discussed so far but if i click on that particular button it simply creates the names of all the worksheets and drops and hyperlink to each one of them so let me show you how to create this button and associate a macro with it i go to developer tab in that you notice an insert option now this is not the same insert tab we happen to use very frequently for pivot table and charts we'll go to developer and then go to insert tab for the time being ignore the remaining activex controls and other form control button just focus on the first button of form control which happens to be the simple button of form control i click on it you notice the cursor sign it's changing and it allows me to draw one button as soon as i release the cursor it immediately asks me what is the macro you want to assign to this button so that when a user clicks upon it it activates and run the macro i press ok i am going to click on this text area and change the heading so that it's more indicative of the task that it is trying to do so update table of contents i click outside to make the button go live and once i do that let me test this i click on this button yes it does happen in fact if you wish to delete this button you will simply right click and say that's all so we saw how to associate a button a click me button with a macro now this time let me show you how to start working with macros through a procedure called record macros we'll be getting inside coding very very soon but what i want us to do is first see how a basic recording macro is done how to run that macro secondly at times you may not know how to code a certain task then we will use some common basic sense and technique and use google's power shift through various google search results and see how can we take some macro and put it to our daily use and then finally we'll start with the normal coding process remember i had mentioned that if you go by chapter one two three four approach you are going to find macros very boring so what i've done is i have tried to inbuild as much action as possible up front so that we can see actionable result and with this let me give an example in this example i have three alphanumeric number on the left hand side and the same thing on the right hand side well typically if you wish to delete all the numeric data from this information so that what you're left with is just this what would you do you would perhaps use control h you would say what find what zero and then say well let me take one digit one because i can see some of the digits one so find what one replace all amongst the chosen data right and with this you will follow with all the subsequent numbers 2 and then 3 and then 4 right well i want that this entire action this entire procedure should be recorded by excel so that later when i require the same action to be undertaken it just runs that in a click of a button let's see how to do that first i choose at least at least two cells why do i say so because if you simply select one cell and use ctrl h it will replace the digit four or 1 throughout the worksheet i don't want that to happen so what i'll do is i'll choose at least two cells if not more and then i go to developer i'm going to click on this button called record macro what it does once i start recording a macro it will record every step it's not a video based recording nor it is a time based recording it's an action based recording so every click every action every text that you write in excel or any cell or any worksheet it will capture that and put that in the vba code we'll see how very very soon so in here i'm going to go to record macro all right let me type in a name which i can remember easily test and purposely i'm giving a space and then writing one why i'll tell you if i press ok right now it tells me that the name you entered is not valid now why so because i have entered a space so the name of the macro does not including space it should be continuous test one i can provide a shortcut key even later i'm ignoring that for now and store macro in this workbook i do not wish to store this in a personal macro workbook because this is computer specific i cannot transfer this macro to my colleague unless i know the intricacies of coding and the visual basic window for the time being i am going to store this in this workbook so that as soon as i transfer this workbook to anybody he or she can also apply the macro i'll discuss on this more later i'm going to press ok but notice as soon as i press ok this button's name will change i press ok notice stop recording has started now i am straight away pressing control h for harmony and i'm saying find what digit 4 okay and i'm going to click on replace all all done we made two replacements fine with me okay and then i'm going to click on stop recording so between the event of start and stop recording what exactly did i do i pressed ctrl h for harmony to activate find and replace and simply deleted the digit 4. now let me do one thing let me again go back to record macro and then say this is test two okay test two fine then if i select these cells my action of selecting the cells is also getting recorded so see the difference let me continue ctrl h this time find what let's say digit 3 and replace all okay close stop the recording if i choose let's say this cell and then go to macros and i say test to run what happens it quickly goes back to the old two cells the same range and does perform the same procedure whereas if i choose the cells again a5 to a7 let's say or maybe even more and then i go to macros and run the test 1 not test 2 test 1. notice what happens yes the number has been deleted based on the selection so selection action sequence is also very important while recording the macro whereby i had recorded two macros one whereby i selected the sales first then went to record macro and then using control h that is find and replace deleted one digit in this next macro test two i had first selected record macro and then chosen the range of cells and then applied the recording of macro now we'll see the codes in here as of now what i will do is go to developer go to macros i find test one and test two right now i don't wish to run this time i wish to see the macro i wish to edit the macro if need be so i go to test one and i click on edit now let me go to the other window i'll talk about this entire panels of window in the next video but as of now what i can see is remember head and tail of a macro i'm going to refer this head and tail quite often so sub n end sub which means this is the one macro another sub and end sub pair means another macro the first macro and the second macro spot the difference apart from the digit spot the difference you'll find this line item is extra in the second macro now why is that because i had categorically first started to record the macro then choose the cell a3 to a4 so my action of selecting those two cells has already been recorded what they're after well the action sequence says amongst the selection please replace three with what nothing and it also gives some attributes regarding the case and the format etc in the find and replace code now what would happen if i were to d this 4 and give letter a instead will the action change when i run the macro let me find out one way to find out is i minimize this window and i may run the code starting from test one or else or else i can go to macro and find test one and then run it notice the letter k has been removed so what we did right now is the very very very basic editing of a certain macro i go to macro and i click on test one or test two and i say edit and then i can proceed with editing the macro now well notice currently the macros which i have recorded are residing under macros tabs test one test two and at the same time the file is xlsx now how's that that possible well if i try to press ctrl s try to save it notice it gives me a window which says the following features cannot be saved in macro free workbook if you wish to save this as a macro free workbook click yes if you click yes and close the file you'll lose all the macro so what i'll do is we'll click no and then follow the recommendation of excel which says please change the file type which i'm going to change to macro enable workbook and safely i can say save this ensures in future if ever close the file and reopen the file the macros are still going to be there so do ensure that you do that while working with macro now we will see how to use the panels of the visual basic window to be able to test the macro better maybe make additions and see how more can use the functionality so i will ask you to go to developer in the file of the macro that which you have recorded going to macros and either one of them i'm going to click on it did and once i do that some of you would get this window either docked fully or maybe partially on your screen it says microsoft visual basic for application now why is it called so typically this is a language visual basic which has been written for an excel application hence it is called microsoft visual basic for application what applications it can be word it can be excel it can be powerpoint so hence in short we say it is vba now what you also notice is uh some of you as you see the screen the codes are present so on the left hand side i also have something called project explorer some of you might not see this some of you might not see this you might see just this so first i'll show you how to activate the project explorer and what does it mean what relevance does it hold so i go to view you notice the shortcut key for project explorer it's given by control r r for rishab i click on project explorer now how is this supposed to be read every excel file that is open will be listed out here and it is a part of a vba project there is also personal macro workbook which is open in the background and that's called personal.xlsb it's in one of the system folders of microsoft windows excel program files now within this notice how the things have been organized if you have 10 worksheets in the file it will be listed as sheet 1 2 3 4 5 6 7. there are names such as modules is a part of the vba project what is the project the entire file now these modules are nothing but the container of the macros code so this what you see before your screen is a code and has been stored in the module module of which file module of this file if you wish to create another module just for simplicity and keep keeping the codes organized you might select this particular project you might say insert and module and what it does it gives you another container whereby if you write a very basic head sub uh test three open bracket close bracket enter notice it creates another basic macro of although it doesn't do anything but that's stored in module two so this is how the hierarchy of the structure of the file moves it has the worksheet it has the module and if we go ahead and get more advanced level we notice we can also insert class module and user form of course that is something i'll be taking up later so let me close all the other window and just show you from scratch view project explorer and view our properties window this talks about each worksheet's property again this will be taken up a little later and by this we understand the basic hierarchy in which the things are stored now let's learn the difference between personal macro workbook and this workbook now where is this term used and where would you find this well if you would have experimented with the record macro button you would have seen that while initiating the record macro it will ask you where do you want to store the macro in one of the option would be saying this workbook and one of the other option will be saying personal macro workbook well this workbook is the active workbook on which i'm working so personal macro workbook and i can send this to my colleague if i want to versus there's a personal macro workbook this is a file which is residing in one of the systems folder and it opens with any instance of excel so even if you're not opening any file and just opening a plain and simple excel this personal macro workbook opens up in the background and you don't notice it until you go to visual basic and in that visual basic notice i have a personal macro workbook which is the active workbook i'm working on and there's another project which i can see and that is called personal.xlsb now there could be a lot of modules in that particular personal macro workbook the advantage is since it opens every time you open excel there are certain macros which you would want to use in any excel file and you do not want to copy the code in every individual excel file so how can you make use of this well let me show you an example let's suppose that you wish you had a macro and what does it do it's going to make sure all the font is let's say times new roman they follow a standard size of 10 and there are no grid lines so simply put let me first activate grid lines and now i'm going to do developer go to record macro macro 2 let me store that in personal macro workbook okay i choose the entire sheet i go to home i turn the font to let's say times new roman enter i change the font to 10 and i go to view and i disable the grid lines so this is a standard procedure that i perform for all new files and i would want to capture this as a part of macro which i did and the best part is anytime i open a new file i can work with this macro so let me stop recording let me close this file i'm going to save this and i'm going to open a new instance of excel so maybe i'm writing my name here maybe the city that i reside in and when i go to developer i go to macro notice it says personal xlsb macro 2 if i run it it chooses entire sheet makes the font size 10 times new roman and no grid lines so this way you can put the frequently used macro in the personal macro workbook now where would you find this excel file which is opening up in the background let me open up those the path is generally in the later versions of windows c drive user xlnx that's the name of the user so most likely it's supposed to be your name app data now this is a hidden folder if you go to this path you may not find app data because the folder may be hidden in which case let me quickly show you how to unhide that folder i am going to control panel i am going to folder options and within folder options i go to view and one of the options says please show hidden files folders and drive in most cases the first option is chosen i ask you to choose the second option temporarily apply okay and once i do that let me just copy this particular path till this point i go to my windows environment in fact it's already open there you notice the path is the same as mentioned in the excel file and this harbors a personal.xlsb file so if you follow this path after unhiding all the hidden folders you'll be able to see this particular file and this is what contains the macro so that's the basic difference between personal macro workbook and this workbook people prefer to use this workbook for majority of the reason why because they would want whatever macro has been recorded they are able to share it with their colleagues over the email so if you send this file right now the personal macro workbook xlsm the colleague will not be able to see the macro why because it has been saved in the personal macro workbook so if you want to share the work please save it in this workbook else if you just want to use it for your own utility every time you work with excel you save the macro in personal macro workbook now let's see some of the ways some of the popular ways with which a macro is executed is run now already recorded a macro using the record button and that ensures whenever i run this the entire font of the sheet is turned into that of times new roman and the size is 10. now i can go to macros button and simply click on run notice the execution so this is the way with which it is run using macros button similarly i can assign a shortcut key to a macro so when i go to macro again this particular work start macro i'm going to go to options button and it asks me do you want to give a shortcut key i say let me give o do remember that whatever shortcut key that you give here it will supersede the existing default shortcut key so for example if you give p it will supersede the printing shortcut key and same goes for s as for save so i'm using a very uncommon letter ctrl o and i press ok and then let me cancel it let me first make sure the font is not timely roman something like uh stencil now i'm going to press ctrl digit o notice it's been run so this is one another way of running the macro apart from this i could have gone to developer visual basic window so in the visual basic editor when i put the cursor before the beginning of the macro i can hit on this run subroutine button for which there's also a shortcut key as you can see called f5 if i click on it boom the entire macro is run and all the fonts have been converted to times new roman so this is another way let me just press ctrl z i had accidentally pressed ctrl v next i could also possibly assign a click me button format control or as we will see later we can also assign a command button as of now i'm just using form controls basic button and as soon as i finish drawing that button it asks me what macro do i want to associate it with i choose this macro and i press ok if i want i can write a different heading something like click here and delete the rest and i click outside so let's see how we run this particular macro first let me make sure the font is changed to something else anything and then let me click on this button oh the macro being run so this is another way of running the macro i know it will take some time for you to go to a stage where you yourself can start coding extensively and perform a lot of tasks which otherwise will take a lot of time to do that manually but at this stage where we're just beginning to get into the coding part let me show you one trick which involves little coding yet at the same time will give you immense benefit what i'm going to do is i'm going to combine the power of google search with a certain common sense technique and a specific technique how to copy the code in the google page or any of the blog post and paste in a certain section of excel and benefit out of the code so take an example let's say we have a set of worksheets which talks about the different countries different cities perhaps and they are all locations and also additionally i have a sheet couple of sheets called working one two and three now what i wish to do is of all the worksheets i want to create an index of names of the worksheets for an easy display of the content for the end user and all these sheet names once they are listed should be linked to their respective worksheets so that when somebody clicks on it it is taken to that worksheet maybe i don't know how to code it well i at least know my question so what i'm going to do is i'm going to go to google and i am going to type in my task what is my task the task could be i wish to create an index of hyperlinked worksheet names or index of links to worksheets we will try to become as specific as possible but do note that please start your search with these three words vba code 2 vba code 2. now what i've found is people do search through internet for getting the codes but they don't add these three important words which are vba code too if you do add these three important magic word vba code to before any tasks that you wish to accomplish your search results will be very specific and accurate so now let me proceed vba code 2 put a verb which signifies what you wish to do and as soon as i write vba code to create it gives me certain hints recommendations you may get the same recommendations may be different this depends on the google rankings and the search results but based on that let me proceed create index okay i'm getting something called index of links to worksheets let me click on it and as the search results come up i start reading the headings of the search result one of it says create links to all sheets in a workbook create a sheet index of excel worksheets both look same although the wordings are different create an index page or table of content now since you gave the term vba code to it is leading you to accurate results which might be a forum which might be a blog post by different authors and since somebody have asked this question in the past they have answered it out of their time and why then we have to reinvent the wheel why do we need to recode it if the code is certainly available on the internet well let me do one thing let me go to create links to all sheets the first one and as this page opens i i use this page quite often for reference it's good good website get digital help once this page loads up i see a blog which says the macro demonstrated below create hyperlinks to all sheets in current workbook oh they'll let their pop-up be closed yeah and i proceed ahead and it says vba code now even if didn't say vba code you notice from our earlier learnings the head and tail of a macro well this is the head and this is the tail that's exactly how i spot a macro even if though it doesn't look differently now if you're using chrome which i will strongly recommend you'll notice on the top right hand side panel you may you may get this tag sign which says view source i will click on it and it opens the window and i need to copy this from head to tail of the macro if you're copying from the window sometimes what happens you try to copy this but it also copies the numbers no i don't need to copy the numbers so you use a technique which allows you to copy only the code part not the numbers so it depends on the website sometimes you can simply select this and get rid of the 1 to 11 digits sometimes you get this view source either ways will copy from head to tail copy that and then how do i use this i go to excel i go to developer now this i'm not recording a macro why because already a user has written that macro for me so i'm going to go to macros i see an empty window okay let me write a dummy name let's say uh x y z okay even a single letter loop for that matter x now as soon as i press x in the macro name excel tell me there is no such macro and hence it has activated the button called create okay fine let me go to create what it does is if i look at the project explorer it takes me right to the workbook which is open and in there there's a module which automatically it has created additionally in the code panel which i see right now before me there's a dummy macro dummy macro means there's a head and there's a tail there's no action in between now how do you paste the macro certainly not pasting in between a head and tail you cannot have two heads and two tails here this is completely erroneous so either you delete this current existing one and then paste it or you might paste below it which is also fine you notice a line has been drawn to demarcate the difference either ways it should not be between a macro let me delete this dummy macro and the name of the macro it says sub create links to all sheets fine i'm just quickly sifting through the various codes although it doesn't make sense to a user like me right now if i'm learning the macros well i'll discuss this a little later at this moment i just wish to see that if i'm able to get hold of a code on the internet and i'm able to copy and paste at the right location of the vba code how do i run and benefit out of it i go to any cell i then go to the macros now since you had deleted the dummy macro and you had inserted a new code the name of the new code being recreate links to all sheets all right let me keep my fingers crossed and run it notice it does exactly what it is meant for it is creating an index of links to all the worksheet did you know how to code perhaps not does that stop you from benefiting out of vba code no what are the common sense needed well define your task what is it that you want to achieve the key words should be relating to excel words so when you refer to workbook it means the workbook of excel do not use file in that case use worksheets use hyperlinks use cells these are all the technical terms that you might use and do what they're after you combine that search task with the three magic words which is vba code two now you get through various search results on the internet and if you're using these three magic words most likely you're going to get more accurate results than not and use that code copy that code go to macros write a dummy name a instantly what you get is create and once you create you might paste that code in here below or maybe above but never in between a head and tail of another macro well in this i am going to ask you to add the power of some common sense of code editing and recording of macro so that the entire google search level which was giving you a certain code can be customized to your task for example in the last video we saw we went to the google and search for a code with respect to adding hyperlinked sheet names and we got hold of a code which looked like this sub create links well when i run this code the problem is it runs from the active cell right what i want to do is whenever i run this code it should start from the cell a2 correct and maybe maybe if need be we also need to add a button which when clicked upon does the same task so let me do one thing let me delete this and then perhaps write a code which will make sure every time a2 cell is selected and then the macro code is run now you might tell me look i don't know how to code so far so how do i then use a basic common sense to add that one line or two lines of codes which make sure a2 is selected well you don't need to at this point in time let's see i go to developer then i go to record macro and then i say test one okay now what i'm going to do is i'm simply selecting cell a2 that is my action and immediately i stop recording so between start and stop recording what is the action that has been taken well it is just the selection of one cell now why is this important why do you see when i go to macros to see whether test one has been recorded correctly i find that name and i'm going to edit that notice in the sub test one till the end i see some comments which is harmless if i delete them but what i see is one line item which says range a2 dot select now we'll be getting into the code very very soon in details right from the very level of object level coding what are the properties attributes but at this moment you can really relate the cause and effect the cause of this code was you have selected the cell the effect as you can see the range a2 does select now using a basic basic common sense what i might want to do is combine this code line with the previous big macro that allows me to create hyperlink sheets well i'm going to copy this simple copy now do i paste it above the head no do i paste below the tail no i will be pasting within the code now without knowing much in detail how do i know which place or location do i paste the code in well we start looking for ideas we'll start looking for hints the hints of this codes tell me that there's some kind of a code which tell me for each work she do this about hyperlinked worksheets well that's my action why not before the action please add some spaces and then paste that code right do i need the remaining set of codes of macro sub procedures no i'm going to delete that now what i have is the name of the macro same old macro great links to all sheets and within that one action which is saying a2 please go ahead and select it okay let me test this macro what i might do is uh although i'll be getting into the correct way of testing the code little later when we start writing the code at this moment i'm going to close this window of visual basic editor and let me go to macros and run this macro notice it selects a2 even if my cursor is on the cell e2 and go to macros and i run the macros it runs from a2 onwards finally just to add a little bit of garnish to my recipe i'm going to go to insert form control button something that we have seen earlier create one simple button and relate the button to a macro sometimes people miss relating this macro so you must choose that categorically okay and perhaps if need be you might give a simple simple heading which might tell you update doc table of contents and click outside let's see if i'm just changing the in name of a sheet to sa the initial and let me go to the button update notice third one changing to essay which if i click upon will take me to the sheet called essay so that was about using google's power a little bit of common sense and recording of macros to modify the code to your taste we'll see more of it as we progress now how your preliminary knowledge of vba code so far combined with the google technique of searching for codes can get us a very good results in this example now this example pertains to a fact where i have five names and they are certain values in the associated column now what does this value indicate this was based on a query which was asked to me by one of my programs participant back in 2013 or 14 and the problem involved a task the task was based on the value a row needs to be added up so if the value says two that means there should be total two rows in which perhaps i want to mention something below that so the case where i have one that means i already have one row no further extra row needs to be added what about the value c well in this case extra four row needs to be added up so i'm pressing f4 f4 function key oops pressing f4 function key allows me to repeat my last action and notice i'm able to add four more columns and notice that i've been able to add four more rows now imagine if the values are not just five or six five in this case but they could stretch up to thousand or maybe ten thousand how in that case do i add rows based on the sales value of course not manually you'll be spending the entire weekend still you may not be sure that everything is correct what we'll take the help of is the special search of google so i'll ask all of you again to go to the window of google and then type in those three magic words which we have discussed earlier vba code to and then specify your task what is that you wish to achieve vba code to insert rows based on what based on cell values right so i'm going to put in insert please note that i avoid using the word add although it's not incorrect but i will perform those such keywords which are more closely associated with the excess working so insert row delete row these are the more technical terms so insert i get insert definitely insert insert rows aha google already gives me some such recommendation i use that now i'm looking at various websites most of themes are very popular based on the user requests they put in the users put in a lot of their answers on board for others help so it says insert row based on cell value looks like something that i need insert row below based on cell value this also looks similar how to auto insert row based on cell value so all these such results one of them could be of your help so i go to one of the four search results click on that it's ausgrade.com and this time i am not getting a blog post i am instead getting a forum forum where users have put in the request that they need a code for some task now what happens in these blogs a lot of users give their answer how do you know which answer will match to your task well i use a simple technique typically if you give some answer and it is accepted and successful then people thank initial coder right now i'll look for those comments which says thank you so much this code worked okay i'm just scrolling this down all right i come to a comment which says woohoo the forum rocks i knew there had to be a similar way to do the same thing and blah blah blah well then that means the code immediately prior to that could be the one that i'm looking for all right now when i look at this code what do you notice the head and the tail the head and the tail of a macro well that is what i'm going to copy now you might ask me look what if i copy some erroneous code code which can be prepared with malicious intent deleting some files well one key is to look at the circumstantial evidence of the user's responses in the forum one it's a very renowned forum secondly users have thanked this user who have given this code so i'm going to copy this i am going to go to the excel file let me open the 2013 version of excel which is what we are working on right now and how do you paste the code well go to developer go to macro simply type in a word a and create now it directly takes you to the dummy shell that it has created do you need this of course not because the copied code already had the head and tail the code name is what try this so sub procedure try this and i'm just looking at a basic basic flow of the code which which has been written to the new user it might not make any sense but can catch up those keywords which are very common so maybe there's some loop it says n greater than 0 then insert and current cell current cell we'll see all of this little later my intention is to get you a hang off the way google can be used to look for codes and you start working on it immediately now logically did not the code did not speak about any particular cell where you might start right you don't find something called a2 a3 a4 or b1 so what i do i place my cursor on this value first value and i go to macro the name of the macro was try this i'm not changing the name and run okay i have one row added none here four more three here and of course two new here imagine the reaction of the user who had initially asked this question to add roles based on 10 000 value he would have been lost had he not applied this particular code so my entire idea is before we jump into coding you can start benefiting out of this vba code just by using my technique of going through vba code 2 typing in the task that you wish to achieve in google get the code identify the head and the tail of a code copy that and paste it in the right module and then at least try whether it works and eventually we'll see how can we customize this further to suit our requirement now you might get confused with object properties methods initially but this example will make this more clear take for example you want an iphone with you right so you have specified the object that the iphone is the thing that you would want now of course you would want to get more specific right what version so there is a property which gets attached to that particular iphone so iphone dot property that's the version now you need to give a value to that version whether it's the iphone 5 or 4 or six that you would want to go for based on your budget so iphone dot version equal to six and that is what you are assigning a value to the property now after this you want to initiate an action action of doing thing which says iphone dot buy so this is a method that you are attaching to that particular iphone now beyond this you would also be want to be more specific in terms of the different parameters that are attached to this method it says iphone.buy how would you want to buy this through the payment type cash or credit card now you can add more parameters depending on the availability of a particular parameters against a particular object or a method so iphone dot buy payment type is cash you want a discount no delivery charge and all this all gets combined in one single line now similar to this is how a particular code is written where my you might choose a particular range of cells you want to specify that what do you wish to do with it now similar to this is what we see something in the coding window itself so if i click on this grouping part you notice active cell dot font so amongst active cells the one that has been chosen the font property what would the name that you would like it to be well i'm going to assign a value which is calibri i may also given a value which might say true for a particular property being bold so these are the ways i can select a particular object define a property and later as you proceed will also also apply methods to the different cells to manipulate them maybe copy and paste to a different sheet and that's how we accomplished a lot of tasks through automation vba now with vba especially for excel things are so intertwined that you cannot move in a very sequential chapter one two three and four format for example in in the couple of recent videos where we were talking about the way the work area must be set what are the different components i was frequently using the words methods properties objects so let me now tell you in brief what do these term mean imagine a house now that is an object within a house you would find various components like doors windows may be furniture right so those are what those are child objects so there's an object house there are child objects like doors windows and furniture now with each of these objects they have properties defined now do could be what uh t could do it could be big door small door now each of these child objects will also have methods attached to it for example what do you want to do with the door either open or close it right so that's an action associated with a certain object so either you can paint the door red or maybe blue now that is an action being performed with a certain defined property right so it's a method that is we are associating with a child object which is a door and the property of the paint color is what red or green now exactly in the same manner worksheet is an object there are child objects within which contain what range of cells each cell has certain property in terms of the fill color in terms of the column width in terms of the cell size and what is it that you wish to do with those cell you may want to increase the size of the cell may want to turn into a color green right so those are the action associated with it so with these basic fundamentals we are going to move along and see how all this fit together in our terminologies while we start coding now since we are all set to start the coding process let me make sure that the work area is very well known to you the work area where we will be starting to code now if i go to developer and if i go to visual basic button let me click on it and alternately i can press the shortcut key alt f11 to access that let me click on it now what you see is a variety of windows which will help you do some specific tasks view the entire project overview so let me do one thing let me close all of them and then i'll start afresh the first thing that i would want on my screen of the visual basic window is i must say please let me view the project explorer now what is project explorer as we just briefly touched upon this topic earlier this is basically showing you an overview of all open files the worksheet names inside them and any module which has been created to store any macro once i click on a module i can double click on it to show the view code window and this code let me see the entire story that's trying to do so now when i look at the code as we already know this is the head this is the tail but let me now get deeper inside the color coding aspect now blue are the text which are specific vba words reserved for certain tasks the black are the ones which are going to be tested during the execution process and has been mentioned by the user now you also notice something which is mentioned in green now what is this green part this is a comment quite often it might happen that you have written a code and you wish to keep it but not get it executed so rather than completely deleting it from the code window i might as well commentify now how do i commend if i either manually i might put a single code before every line item but as you can see that's a very tedious task alternatively what i might do is select the entire group of text which i want to comment define now since i've already commentified what i'm going to do is uncomment the block and there's a button called command d block now you might not see this toolbar on your screen and to activate this what needs to be done so i go to view i go to toolbar and then there is an option which says edit if it is turned off you will not find that toolbar so make sure you have the toolbar by going to view tab and going to toolbars and clicking on edit one and once i have tested the code and i wish to go back and make sure this is the comment which has been removed and converted into an actual code i might simply say uncomment so this was one of the cds of knowing your workspace by the way why is it looking red because this is no command which says loop for all worksheet from our initial example the first supposed to be commented now let's see what else can we do with the edit toolbar on the top right of this code window which we saw how to activate now within this apart from the comment and uncomment block the next thing is you can use a certain button which is called increase and decrease indent to make sure you have the effect of tab key and shift tab key to make it go back to the original stance apart from that sometimes it happens that in the same file in the same project you might have inserted another module and you're working with another module so let's say i'm just giving sub test five enter now what happens is i wish to toggle to different points of code from different modules so what i do is i go to module one i perhaps want to keep a track of this particular line item of code and i may have to toggle back and forth so what i do is i select this particular line item i then make a bookmark now since it was the first time a bookmark has been created you notice the blue squarish almost rectangle looking like box appearing here now let me go to module 2 and then if i say you know please help me toggle to the next previous bookmark or next bookmark let me click on it what does it do it takes me instantly to that part of the code now this is useful generally genuine cases where you are going to write long list of codes and it's very easy to get lost amongst the codes that you have written in different modules so you want to create place placemarks bookmarks or traces traces that can help you move back and forth between those important lines of codes so this was about indent decrease intent and about the bookmark now assume that i am going to write a code which starts from r a n g now i wish to complete this entire word so what i might do is i might go to this button which says complete the word now notice that the shortcut key associated with it control space so if i press ctrl space notice it completes the range object now i would want to know what are the different objects properties methods associated with it so i will go to this button which says list properties and methods again notice as usual there's a shortcut key associated with it so i'm going to press ctrl j and it gives me a drop down where you see an object various properties and methods and the whole list of drop down where you can pick and choose so let's say i have selected dot select that's one of the methods select now what i wish to do is i wish to find out if there are any parameter associated with this particular method so what i do is i go to quick info and it shows me a small parameter group now what would have happened had i chosen just the range object object method property something that i'm going to discuss very very soon in some time notice i quickly click on quick info and i get the all properties of the range object so the couple of shortcut keys that's going to help you along the path is ctrl j ctrl i and control space now there are many more other shortcut keys which i will discuss as and when the relevance arises as of now even if you are not able to remember the shortcut key that's fine what we'll be doing is this is just an overview and eventually we'll be making use of them and that's the point where we will start learning them and remembering them now as a continued series of knowing your work area let me just close in on few of these remaining residual issues if i go to visual basic window how do i test a certain code once i've written so so let me double click on the heading of this microsoft visual basic for application window minimize this now there are various methods through which users developers test their code and i'll illustrate some of them some of the popular ones for example this is a very simple code which selects the cell a2 and creates a list of all the worksheet names along with the hyperlink now how do i know it's going to work well let me do one thing let me place my cursor at the beginning of the statement of this sub procedure or a macro beginning now i'm going to press the shortcut key f8 now what is f8 function key going to do it's going to run the code one line at a time notice how it toggles to the action of selecting a2 then it says for each sheet in this active workbook please apply the sheet name in one of the cells fill it up and then do also apply a hyperlink inside them so notice it toggles one by one and on the left hand side how the cursor selection is moving yes so the function key f8 key allows you to quickly toggle through the various statement and see what they perform let me do one thing let me reset the entire code so that it stops midway the second way through which people users developer test the code is using this particular run sub user form so run a sub procedure or user form now when i click on this notice it runs the code entirely at one go it doesn't stop midway so f8 function key allows you to see the codes effect one line at a time whereas the function key f5 runs the code at what in an instant now those are the two methods to which people do test the code alternatively sometimes what happens you wish to just test one part of the code so what i might do is i place my cursor here at the beginning of the statement i press f8 function key now i can hold on to this yellow arrow and bring it at the line item which straightaway says please create those names the worksheet with hyperlink notice i have skipped the entire line item which says please select a2 now if i continue to press f8 let's see what's going to happen notice the names of the worksheet are appearing from the 12th and the 13th onwards well that's because i just broke the sequence and moved the yellow arrow and started midway so this is another way whereby you don't want to test the entire piece of code just one small piece of code just to see whether it is doing what is meant to do so there are three options f5 f8 and using this toggle point arrow well if you are going to run a long list of codes at times you will have to test the variable and how are they calculating the logics that you have tried to incorporate so one of the tools available to you is immediate window now where is that if you go to the visual basic editor right on top and you go to view menu you'll be able to see immediate window you can also activate this by pressing ctrl g i click on it and at the bottom right section i have this immediate window popping up now how is this used what is the utility let me explain if i begin the code and i start pressing f8 a shortcut key which allows you to run the code one line at a time now this yellow marker indicates the next line of code will be run is the one which is highlighted so it's not been run yet x equal to 10 but when i press f8 it will be so at this moment the current value of x equal to what 10 and if i run this line of code 10 multiplied with 10 will be the reassigned value of x thus making it 100 now at this juncture before this line of code is run let me reassign the value of x x equal to let's say 8 and i press enter now what just happened was without changing the code without changing the internal logics i have been able to reassign x to the value of 8. now if i run the code by pressing f8 and thereafter if i try to find out what is the revised value of x so i'm not pressing enter in the immediate window in fact i'm just putting my cursor at the line below the first one and put in question x enter notice it is 8 multiplied with 8 that is 64. now this allows you to test the code without actually changing the variables of the code itself because if you try to change the variable somewhere down the line you may forget to undo that and your entire code might be corrupted in logic other than assigning values and trying to find what a variable's value is i can also use regular commands for example i can put question mark and i can write worksheets dot count enter it gives me there's only one worksheet in this active workbook okay let's take one more example what if i put question mark and write range within double quote a1 double quote close bracket close dot value since i've put a question mark and trying to ask the immediate window what is the current value of a1 it does give me the answer 12. so at a very basic level immediate window helps you reassign the value to a variable check out what is the current value of variable and also help you ask a lot of questions without exiting this immediate window so see more of it as we go along now in this session let's learn about one of the most important objects in vba and that is a range object now what is range object typically any cell that we select it is defined by an address which we see in the name box it's called b2 in this case uh c2 in this case and if you happen to choose a group of cells something like this it's a range defined by the starting point being c2 and the ending point being d5 now all these are examples of range most often they're not since your data is stored in these cells in ranges of different worksheet which you want to manipulate cut copy paste shift it to another cells and this is the prime reason why range object is so important because unless you know how to work with it you cannot manipulate the way they are going to be treated by you in a very customized manner so let's see how we progress on the learning path on this range object first let me do a small experiment what i'm going to ask you to do is go to developer right click on record macro and i'm just saying the word select one as the name of the macro and which has been stored in this workbook so that i can store this in this workbook and save it for later use also maybe transfer this to you you can make use of it now when i press ok i'm going to choose a group of cells and that's what i've done and simply and simply turning the color into yellow now once i do that let me go to developer and then let me say stop recording i'm so curious to see what the code has been written in the background by the visual basic editor once i started recording the macro let me go to macros button i see the name select one appearing let me click on edit alternatively i could have gone to visual basic and i would have seen that this is the file name on which we are working within which contains one module or with the container or the storage area for this macro now as we know from our previous basic discussion every macro has the head and the tail i can see that and so i can see is a couple of statements which have been commentify do i need this no the first line that we had the worst action rather that we had done was choosing a particular range so what has happened is there is a range that has been selected and the action says please select it now with that selection do what with the selection the interiors please change the color to a certain value which has been specified as six double five three five that's the number for yellow did we talk about anything other than the color no but excel by default gives these default settings turn to null or default value now if we were to write the same thing using the coding process how would we have done so let me do one thing let me go inside this particular project which is a workbook name and insert a different module so that we can work simultaneously with the next macro so what i'm going to do is i'm going to give this name sub select 2 bracket open close that's the parenthesis pair press enter automatically the tail of the macro comes up now what i'm going to do is i'm going to use all the shortcuts that we had discussed earlier so i'm pressing r a n g e notice as i press dot intellisense comes to my help giving me all the properties and methods that i possibly can apply with this range object so as i start typing in s it gives me select now select that's that's the method let me click on it and what it does is range.select now you notice i have made it committed an error why let me start running this and let's see what happens so i'm going to click on the start of the macro and click on run compile error why is that because after range i did not specify the value now what would could have helped me specify the value if you press ctrl i that's the shortcut key that will give the different parameters associated with it so i'm going to open the bracket it asks me what cell that i want to choose i'm going to put that in double quotation a1 colon and let's say b2 double condition closed and bracket closed now i'm going to press dot and the intellisense now tells me what exactly needs to be done with this range so i'm going to again go to select press the tab key and that's about it let me click on reset the entire macro and then run that again notice a1 to b2 has been chosen so this is one way of specifying the range now as a continued series of this range object let's see other ways to select a particular range now the last time we had seen how a range can be defined like this and selected let me commentify this let me look for that particular window which lets me comment if i yeah now there is an object which says cells and it helps me tell specify what row number and column number intersection is the cell i am wish to refer to so cells one comma two is reflect row one which is this one column number two and the intersection is what b1 what i'm wish to do with that is select since i had not given that method it gave me that error so if i now run the particular macro let's see what's going to happen notice the intersection of row 1 and column 2 happens to be the cell b1 and that's what has been selected similarly let me manipulate this to let's say two so row number two column number two refers to what b2 let me run the macro again all right this is choosing b2 so this is another way of choosing a particular cell or a range now how about combining both of them well at times what happens when you wish to give variables so that the row and the column number references change in which case you would want to combine the range and cells object so range i press ctrl i it allows me to see what's there i need to put in that is a bracket open now within which i'm going to put cells open bracket close bracket i'm going to come back to this particular parameter and value but till then let me add one more cells close the parenthesis now notice it is asking for the starting point just like a1 and the ending point now how would you define a1 which row which column number intersection row 1 column 1 right similarly how do you define b1 it is row 1 column number 2 right now once this range has been selected i may later want to manipulate by copying and pasting to a different worksheet so at this moment i just wish to check whether what i have written is selecting the a1 and b2 both together let me put my cursor beginning and click on this run notice a1 and b1 has been chosen so besides the other two ways and approaches to select cell or ranges i have one more combination which is going to be extremely beneficial especially when you wish to run a certain activity in loop and once a1 b1 has been selected the next action should happen on the cells below and below and below until a certain point in time and that's where the combination of loop variable declaration and the current method of choosing a range will help us to do make sure that you do experiment with at least these three ways of choosing the range there are many more but i'll stick to this one at this moment because i don't want to burden you with lots and lots of codes uh half of which may not be relevant at this point in time now with all the combined knowledge that we've had looking at all these video in the past let's do a very very small project and let me explain what the steps are going to be now in column a i have six months listed out jan to june and what i want is through a simple macro which loops through and that should delete the second the fourth and the sixth item all even now how do i do that based on the knowledge we have had and plus some extra knowledge which i'm going to get into more detail as we progress in the course so what i'm going to do is i'm going to go to developer i'm going to go to visual basic all right now if you ever want to toggle between the excel window and the visual basic window a very important shortcut key is alt f11 i repeat it is alt f11 if you press that notice how quickly you are able to toggle right so let me do one thing let me now place all the window correctly all f11 now what i wish to do is write a sub procedure so sub and this is a delete even items bracket open close and enter now let me just ensure that the first letter of every uh word unique word is capitalize it helps in looking at the name of the macro very easily when you are going to run it so delete even items now as i press enter now what i wish to do is select the alternate cell starting from second then the fourth and then the sixth right so how do you select the cell cells and i'm going to say row number one oh no it's row number two and column number one which needs to be selected right and then i say equal to what equal to nothing let me copy paste this three times and in all these cases i'm going to say row number four row number six let me see what happens if i run this particular macro now i'm going to press f8 f8 that's the shortcut key to make sure the command line is run one at a time f8 f8 once this macro is run notice the february has gone away right now uh as i hover my cursor on this selection notice what has gonna happen f8 f8 and that's the end of the procedure now the same thing i could have written with a help of a loop and a variable although i'll be discussing this concept little later but i'll just quickly show you how does it ultimately appear what is the big picture let me retain all the values back jan to joon i'm going to commend defy this part right and then let me start writing the same thing again in fact let me just copy this for the time being now notice if somehow can i get a mechanism by which this variable changes to 2 4 and then 6 and then stops the entire procedure so what i'm going to do is i'm going to put a variable called i now the starting value of i should be 2 because i want a sequence of 246. now there are concepts like declaring a variable those are good practices in detail i'll discuss them later now i is equal to agreed and currently since it is 2 that 2 1 refers to which which cell row 2 column 1 that refers to february now there should be someone to tell this macro that once you have covered the second row please jump to the fourth row so there is a command which will say please reassign the value of i i is equal to i plus 2 so that once it has taken the value of 2 next time it will take the value of 4 and next time it's taking the value of 6. now you don't want to repeat that endlessly now do you want to just make it happen once you want it to happen three times right so what i wish to do is i'm going to put this entire piece of action in a loop now loop is something again it's very very important but i'll just show an overview how this is portrayed so do while i is less than seven and loop also has a head and a tail so this was the head which specified the condition and the tail is loop the word loop so do while i is less than 7 till the time i has the value less than 7 please do this task again so let me test this how it's going to look like i'm going to press f8 yeah f8 now i'm going to press f8 again so what is the current value of i i is the value 2 as you can see from the key tip now i is the value 2 so 2 and 1 r 2 c 1 which refers to blank delete them reassign the value of i and y and once the value has been reassigned notice what is the value of i it is now 4 from the key tip let me rerun that again what is the revised value of i let's find out i is 6 is 6 less than 7 yes that means continue running the macro now once the i has been reassigned the value of 6 plus 2 that is 8 is 8 less than seven no hence the entire loop has been superseded and then straight away to the tail of the macro so this is a very very very simple use of the variable and the loop and that's the reason i focused on the range object especially the cells one which allows you to put variables inside its parameter and which can be manipulated and looped through we'll see more of it as the action intensifies now since we are already discussing about the range object in terms of selecting the specified ranges let me also specify the two lines of code which helps you select a particular set of rows or columns now before asked lies one item which says rows in double quotation five colon seven double quotation close dot select and similarly for column where you can specify the start and end of the range so let me test this one by one the first code let me execute it let's see what happens notice row number five till seven selected similarly i'm going to comment define that and remove the command from the next one and let me rerun the code again notice column b till e has been chosen yeah so these are the two ways i can also choose rows and columns apart from the other methods and properties that we saw earlier now let's experiment with some more examples of range object and how can we manipulate the selected cells with a certain number so what i'm going to do is i'm going to choose this particular project and insert a new module right and this time let me give a name sub the idea is to select cells right open the bracket close the bracket the parenthesis complete enter now if i specify range dot it gives me of course a lot of values of methods but before that let me specify what is the range that i want to select right sometimes initially when you're starting with vba this is the point where you will make most of the time the mistake of not being specifying the range so here i put a double quote and select a1 colon c3 double quotation closed brackets closed dot do you want to put a value against it or do you want to say select it at this moment let me try if i simply can say equal to 12 and if i press enter at this moment it doesn't show me a red text so let me run this macro and see what it does yep instantly all the selected cells amongst the ranges turns to two now the same thing let me copy it and paste it twice and the first one i'm going to put a comment against and this range i'm going to put a dot and let me see if i can get a particular property let's say value and is it 12 let me return the macro it does the same thing again let me just change the value to 121 just to differentiate and let me run the macro again right so sometimes by default it catches the value sometimes you'll have to specify this of course it's a good idea good practice to specify exactly which property you want to change and what method do you want to apply so this was a very very simple and basic examples how can you put values against the selected range now after having written some very very basic code regarding range object and how to manipulate cells let me show you a way through which you should actually test your macro a lot of people refer this as a debugging of the code as well let me show you what this refers to let's say for example i have manipulated the cell a1 only to a value of 121 and simultaneously thereafter i'm going to copy this particular part paste it below and then i'm going to remove the comment single quote and change the value to 12. now ideally the first value that should change is the a1 121 and thereafter all the remaining values should again be superseded and then rename 12. so let me delete one thing this one go back to my visual basic window right and then then let me select the first line of the macro code let me press f8 it gives that yellow selection all right let me again press f8 it allows you to run the code one at a time now as i press f8 notice it has entered 121 right now currently it is about to supersede all the range values with 12 so which i'm going to say f 8 again notice now it supersedes the existing value of a1 to c3 by 12. notice my initial selection was more but that doesn't affect the macro which specify the value that should changes a1 to c3 and not a1 to c4 so that's about simple technique of how can you test your macro hmm so after that last task i am going to ask you the second question after looking at the code which you find on my screen what do you think this macro is going to do it's going to yes yes select the cell a1 and put what a text string and that's the reason you will notice the text string has been given in a pair of double quotation and after which the next cell below that is going to turn into one let me put my cursor before the beginning of macro and run it yes as you can see serial number and that's the digit 1 below that so do remember whenever you add text string to a particular value you must put that double quotation unlike a number which may or may not require the double quotation to put there in now this time i have a mini task for you with respect to range selection and manipulation well the task is using a single line of command within the head and tail of a macro please make sure all the values which you now see on my screen are turned to 14. now if you use a traditional method you would have surely gone to range bracket open double quotation open you would have said a1 colon and that would you would have specified the end of the range which is what c3 double rotation closed bracket close and equal to 14. now what you would have done is you would have certainly copy pasted this below and change the value let's say in this case e4 is at e4 yes it is e4 e4 till e7 all right e7 now idea is both these lines will together accomplish the task that you have set to achieve but my wish is that you turn this into one single line of code so what else can we do let me just put a comment in here and i'm going to write the same thing but this time with the twist which allows me to manipulate different ranges in a single command so notice within the double quotation i am copying the range e4 to e7 and putting a comma and then pasting it now what this does is these is a union b which means what this and this both are part of the same range and that is going to turn to 14. so let me do one thing let me just change the value to let's say 17 just to test and i'm going to run this macro boom all turning to 17. so this is one way through which you can select different scattered ranges in one single command and turn into a value or a formula which later we'll see and that too in a single statement that that's fast that's fast so ensure that you complete this task before we move on to the next one now this time let me show you how to use range object with the find names named ranges and how to manipulate through the various numbers now let's assume that i'm going to choose a group of cells that's a1 to a4 and give a name something like data and enter now once you press enter henceforth this entire range will be known as the data and once you choose that it will directly take you there now if i can quickly go to visual basic add a module insert module and let me give this name sub testing named ranges all right open and closing the bracket instantly as i press enter the tail of the macro comes up now i say range within parenthesis pair double quotation and then i put the data the name of the range that i had given double quotation closed oops i pressed enter by mistake ah let me now close the parenthesis and say equal to one to one enter now let me run this macro and see what's happened let me first in effective manner let me not choose the same range just to test whether my action of the macro is performing well and then let me go to the window and click on run all right so all the range which was or named the data has been manipulated one to one so now let me do one thing let me go to formula and name manager and once i'm inside there let me change the references of the name data i go to edit the name remains same but i'm just going to increase the selection to let's say 10 so from a1 to a10 now when i run the macro let's see what happens so i go to developer i go to macros and i go to this macro which says testing name ranges and say run right that's a very important technique when you are going to combine the small technique with a bigger set of macros later point in time why because with a certain logic the name range reference might change maybe more may be less and based on that the action will be performed so you would not have to interfere within the action of specifying the range manually we'll see in this video session we'll see how to choose selection or ranges other than that the methods that we have seen already and how to change the properties at a very basic level so for example if your event says whatever the active cell right now i would want to change that to the color yellow okay the code for that is active cell dot interior i'm going to use the interior from the drop down of intellisense press tab key dot color and pressing tab key so active cell whichever is the cell which has been chosen already if i say equal to 6 5 3 5 and press enter and let me now test this code from the beginning notice it quickly colors the cells which has been the active cell now on the contrary sometimes you would want to choose a range of cell before you run the macro and as you run the macro the selection the entire selection should be colored off right so what do i do i'm simply copying the secondary part of this code and then i'm writing this code saying selection now selection refers to the entire gamut of cells that you have selected and then i paste the secondary part of the code with the dot in between and let's see what this does let me run the code aha entire selection got colored so besides this besides this what if i wish to ensure that the entire sheet is selected and colored yellow well before you do that before you do that that one more line of code that you need to write that is cells dot select so the first line of code says the entire group of cells in the sheet will be selected and since it's selected the next line of code suggests that selection please make it color 60535 which is the code for yellow let me test this out whoa so this works fine for all of us and this is how you may also alternatively choose the selection ourselves and change the property for them

Channel: Yoda Learning Official

Views: 14,330

Rating: 4.9011235 out of 5

Keywords: excel vba tutorial for beginners, excel vba programming, excel vba full course, vba tutorial for beginners, vba code in excel, excel vba 2021, yodalearning, yoda learning academy, excel vba tutorial, excel macros for beginners, excel tutorial, vba beginner, vba beginner tutorial, excel visual basic editor, excel macros, excel vba and macros, excel vba basics, excel vba introduction, excel vba beginner tutorial, vba tutorial, vba excel programming tutorial, excel vba course

Id: TMdxLaRlt5M

Length: 97min 39sec (5859 seconds)

Published: Tue Feb 09 2021