Welcome to this new video, I am Miguel Vela from your channel El Tio Tech and today we are going to learn how to use macros in excel. The first thing we need is to download this spreadsheet, I leave the link in the description of it video so that you can download it are exactly five exercises so that you understand perfectly what a macro is how a macro works and what it is good for you and I am going to start I am going to start this complete guide of macros by saying the following you need understand that macros are divided into two parts one is the macro recorder of macros and the programming of macros with the VB language which means visual basic application what we are going to see in this guide in this small course of macros is only the first part the macro recorder that we can do by clicking but instead the second part, which is already somewhat longer, would take approximately two or more months , obviously depending on the effort we put into it ngas but macro programming is used here ok this is a course of months with the language again I tell you visual basicapplication what we are going to see is only a macro recorder well so we are going to start with this and the first thing we are going to say what are the macros the macros that we use to automate tasks the macros execute a personalized set of actions with just one click the main objective of the macros is to facilitate the excel tasks that you can perform constantly that you perform every day the macros without a doubt they are wonderful to save you time from activities that you do constantly well let's go now yes and grab the spreadsheet and we are going to see some exercises but before they come but before so that the macro can be executed you have to save it look at it you have to save it enabled for max ok how do we enable any excel workbook for macros ok let's go to the file tab let's go to save since we are going to examine it and we are going to put it anywhere I am going to put it on the desktop and here where it says type we have to put the macro-enabled excel workbook on it we click here look at the macro-enabled excel workbook macros and after that we put the name you want no I 'm going to put excel macros for example I hit save and now yes until the first part one enabled for macros 22 we have to enable the option or the programmer and developer tab ok programmer and developer where is that little tab look here here if we go up to the top it says programmer or you can also find it as a developer in case neither programmer nor developer is there we have to activate it we are going to go to the file tab and after that we are going to go to go down to the last options and here in options we are going to go to the left where it says customize ribbon click here and to the right look to the right here we have the tabs main this option that says programmer again I tell you you can find it as a developer you mark it you put the check and then we give it accept and you will have here this little tab that says programmed ok here we have it after that on the left I want you to observe here it says record macro ok but let's go let's do the exercises a thousand here it tells me to change sheets ok the first exercise is that we change a macro that changes sheets how do we do that look but before executing a macro we have to assign a vote to it ok this button could easily be an image or it could also be a form more quickly look I'm going to come here insert tab and I'm going to go I'm going to go to forms and I'm going to look for a little button that refers to what I have to go to another sheet, for example, I could use this middle arrow, they tell me , I click and I'm going to put it here, for example, no obviously you can change the color of the ball here, I'm going to put look, I'm going to put exercise here ra that you send me to exercise 4 gay we can change the color we are going to change the bold color so that it stands out more then this little arrow is going to call a macro we are not creating the macro yet and it has to take me where to exercise 4 then we are going to finally record our first macro we go to the programmer tab and we go here where it says record macro I click and this window that we are observing will pop up, it asks me first for the name of the macro I could put it to see Let's see, we're going to put an exercise here, let's see, let's see exercise 4, for example, that's how it's going to be called exercise 4. I accept it and I want you to observe that here on the upper left a small square button appears and it says stop degree of recording that it means that the macro is already executing remember that everything you do from now on when the macro is executing will be saved ok and it will be saved internally in codes for example if I click here in exercise cicio 4 look I'm going to click and the macro has just recorded that click and that click that I gave it where here on sheet 4 so my first exercise and it's just going to sheet 4 and I'm going to stop recording ok like this The only thing this macro did was click on sheet 4, so if we come here to the first exercise and we can now assign the macro that we just recorded to this little button, we right-click it and we assign macro, look at this form, assign macro and here it tells me exercise 4 I give it to accept look I click outside and if I pass the mouse if I pass the mouse over it in this way the little hand appears to click if I click look it has just taken me and that's it I just took the 4 - again exercise 4 again exercise 4 I click and it has just taken me in the same way here I could put another little button so that I return to the main exercise which is this we are not going to do it look I am going to the insert forms tab again and we are going to put a little button to come back to me we're going to put the face look at this ok we're going to put it over here big yellow color right click edit text I'm going to put it back ok we're going to put it back we're going to put bigger bold bigger bigger and now yes the same thing look let's go to the programmer tab at the same time we are going to go to record macros and we are going to put here that it takes me to the exercise the name that you give it does not really matter it is only a reference ok it is only a reference but remember so that you do not use another macro and we click accept look I click accept the macro and everything I do is already being recorded and I'm just going to click on exercise I'll click and that's enough I'm going to stop recording and now I'm here here look I right click assign macro and I call it here to the exercise I do not accept now yes let 's see I'm going to exercise I click on my first macro which is exercise 4 and if I go back I click and it returns me look now it is I am using the macros to navigate between the sheets of excel ok we still don't see practical exercises that can actually be useful we are not just seeing how the macros work well now we are going with my second exercise here put size look let's go first we are going to choose a cell I am going to choose, for example, it is to do what I am going to put in another color or simply in white that is here, I am going to put it, look, I am going to put it here, I am going to put a text, uncle text, for example, not uncle text there we have the text uncle well here I am going to transform all the ones he asks me he tells me to put size 26 bold italic to a cell that is the cell and I am choosing a random cell which is this one now we are going to put our little button we are going to put our little button I am going to put it to see I am going to put here what name we could give it to see I am going to put one to see only 120 is this I am going to put its reference to each one to this I am calling this exercise 1 game the t exto centers it day to your liking well now if this little button is going to execute everything it asks me here ok then let's go again let's go to the programmer tab we are going to record macro and we are going to put the first name and I am going to put one ok this is the reference name 1 I give it to accept and from now on everything I do don't forget the macro it's going to start recording and I'm going to click on the cell look at that click I gave to the cell the macro and it has succeeded then I am going to go to the tab I start this also the macro and it is already recording it and it asks me to put size 26 here look I already have the cell selected I am going to come here and I am going to put size 26 this size 26 the macro also recorded me after that bold I'm going to come here I'm going to put it in bold and it asks me for italics and I give it in italics all this movement that I just made the macro and we've done it ok I'm going to programmer I'm going to stop and now we are going to assign here here we are going to assign and I am going to assign the name one q ue is the name that we gave to the macro we do not accept this look I am going to change it I am going to remove everything I am going to remove everything 9 and we are going to give it here again in 12 a click and look how it has just changed me and It has just changed me , look again at twisting, let's see, it goes back, I'm going to put up to 5, look at 5 , I'll give it 1, and it has just executed all the tasks it does for me, it's asking me not to size 26, bold italics, to a cell, look, let 's go now if this one I'm going to call it 2 ok so I don't know the name of the macro I'm going to insert again another little button and here I'm going to put it let's see we're going to put yellow color on it right click edit text we're going to put 2 ok we're going to put 2 look this is my little button that is going to call the second macro that we are going to use well here it is asking me for size 12 deactivate bold and italics that is everything that this one does we are going to change it ok it will be the opposite then let 's go to go back to the programmer tab we are going to go to record macro and here we are going to call it 2 this is the name of this macro we are going to accept we are going to accept and I am going to click first on the cell that I want to change I click the macro has already recorded it I go to the start tab and I am going to deactivate bold I am going to deactivate italics and here it asks me for size 12 there I have it we are going to come to programmer and we are going to stop the recording we click on stop recording and now we are going to give it look we are going to give first click on one it executes us we are going to give it one click to see sorry we missed calling the macro we missed assigning the macro right click assign macro and we are going to put the number two this is the name of the macro two we are going to accept and look one is what we are observing and two I already changed 1 2 1 2 then these macros are executing in the actions that we just recorded where we see the macros where the macros are stored look if we come here to exercise 1 we right click it and we give it see code see code this little window that you are observing is called the window of v isual you see application here the macros are programmed here all the coding that you do with pure clicks is stored here but where exactly are they look to the left we have here the main project and we have two folders the first folder stores all the sheets what is here are all the sheets you have here in your excel or gamer in sheet one is called exercise and so on sheet two is called exercise 1 etc. but the macros are here in modules if you click twice on module one look you open here you open folder two times click to the right you will find all the macros that we are using look at how a macro is composed you do not need programming to know programming but for culture so that they know the macro begins with the words sub sap sap and then comes the name that you assign the name for example could be one the name that we put to a macro no and you open parentheses saw parentheses after that as it ends it ends with the tap ok end with this this is the structure of a macro everything you do is going to come here what is going to come everything you do to face with clicks everything you do with clicks but in programming this is how it starts and this is how it ends everything you record is going to be in the middle of the name of the macro and enter it in sap so you will find it look for example at exercise 4 which was the little button that took us look it starts with sap the name you assign it opens closes parenthesis and ends here and this code that you see here is the one that is in charge of taking you to exercise 4 this or this line of code is the beige application visual programming we are not going to see it but so that you understand it ok look at one look at everything everything stalin all this line of code executes macro 1 and it is for us to put the font 26 bold italic etc. well here we have it this is how macros work if you want to modify a macro you can do it directly with the code but you would have to know visual programming times application the other the other is easily you can delete the macro delete it you re-create it you re-record it until it comes out correctly ok so you already know if you want to modify it easily it comes easily you don't delete for example I'm going to delete 1 you simply select them look here the selected ones and delete and delete the code and here 1 no longer works it is also and I click on it an error will pop up because it is already deleted you re-record your macro well in you re-record your macro and you tell him, for example, one I'm going to put here 1 2022 for example I don't click here to see it doesn't allow me numbers sorry it does n't allow one with more I'm going to put it I'm going to accept it and record your macro again you put 26 they put it in bold italics programmer stop and re-assign look I right click assign macro and home my other macro look I already have it here my other gay macro we give it 1 and we give it on and that way look I just recorded one again macro but as another name in case there is an error I will record it again well there is no problem now we are going with exercise number one where they ask me to order the format of this table ok this is actually free look I am going to assign it the macro to this image this is an image that we are looking at or we are going to re-insert another image insert images this device and I am going to put this check look at this check it is an image that I am going to put smaller and here here with this check we are going to work here we are going to assign the macro to this image but the first thing I am going to do is select the first thing we are going to do is record our macro ok programmer click programmer record macro and we are going to give this macro a name and I I'm going to put order order more we click accept and look I'm going to select my entire table to select all this and the macro recorder is processing it for me in codes and it already has it stored we are going to weigh start we'll put a little board on it we'll put borders on it I'm going to put all the left on it I'm going to put all the left on it I'm going to select the one in the header I'm going to put, for example , a yellowish one to see what else we can do to date look at the date I'll go to select and I am going to put a long date on it ok look I am going to bring it more here to see to see more here we have it good and that is only what I want it to do we are going to come programmer stop recording we are going to assign this image right click assign macro and we are going to put the macro that we just created which is to order macro and we give it accept see and it is already working this is already working what happens if I mess it up or are we going to mess it up yes we are going to put this without filling and again we are going to put a short date on the date there we have it if I click on the macro look what it is going to do I click on the check and look how it has just returned to the same format no but come on let's see more exercises look here let's see They tell me the four basic operations which are addition, subtraction, multiplication and division. Look, let's do it quickly . change the color and here is going to be the result ok here is going to be the result let's put let's put here look I'm going to put four little buttons with sweet with w copy paste with sweet control v copy and paste and there are batches ok right click edit text schwazer addition I'm going to put more right click edit text subtraction right click edit text I'm going to put multiplication and right click edit text we're going to put division there we have it we're going to center we're going to center here look first what it's going to be good there we have it they are already here we are going to put it more to the right we are going to put it here and we are going to put it here ok I imagine that you can already imagine how we are going to work and we are going to put it here we are going to record time yes programmer to the left to record macro and we are going to put here first sum ok we click on accept we are going to put we are going to click on the result we are going to put equal and I am going to tell him to add this cell plus the cell of below I press enter and down here I click twice click and I am going to put sum nothing else sum nothing else impressed people now if I go to programmer and here stop recording I right click we give it assign macro and we are going to put sum here it is sub I give it to accept and now I can delete look now I can delete all this if I put 10 kapono 20 here and I click on sum look I have just added everything perfect no now we are going to record the subtraction we go to the record tab we say here subtraction ok subtraction macro name let's click accept and I'm going to delete the word sum I'm going to delete the result and here we're going to put equal and we're going to select the first cell - the second cell we press enter and down we go to put twice click subtract now if pr we activate center we go to the programmer tab and stop recording right click we assign assign macro and we subtract and we accept look now if it adds 30 remains there we have it sum subtracts sum subtracts ok if I put here 20 and here we can 5 if I hit sum there we have it 25 if the subtraction is 15 perfect no again we are going to programmer we are going to record macro and here we are going to put multiplication multiplication we accept we eliminate we delete all this and again we put the same number one by the number two we press center and we put multiplication and enter again we go to programmer and stop recording we assign it right click assign macro and we give it multiplication and we accept the same we are going to do with division the programmer record macro here we are going to put division we give it 6 accept and eliminate this cell we eliminate this other cell again we put the same the number 1 between the number 2 we press enter and here we put division and press enter again and now yes in programmer we are going to stop recording the only thing missing here is to assign the macros here sorry in the division right click assign macro and we give it division twice click now yes we are going to see if it works we are going to put 10 and here we are going to put 2 that I want sum there it is there we have it sub I want to subtract 8 I want multiplication 20 and I want division 5 look sum subtraction multiplication and division then this macro is already working perfectly now let's go to exercise number 3 which would be to search while you write this if it is a case that you it can be useful in real life I am going to have more here more requirement to see insert insert well what we are going to do is search while you write look at this entire table we could give each tab start insert and I am going to give it tabs be in the table because I do this in case you want to increase more records it is necessary that you think you have to create the table ok and we accept now yes what we are going to do and s copy the one above the header right click copy and I'm going to put it on top right click paste special only values only values there I have it and I'm going to select it and I'm going to put it like this ok as we are observing look first and I'll go to do without macros and then you will see the utility is to insert forms and we are going to put look here there is going to be the little button ok to edit and we are going to give it search this is going to be a search engine look we are going to create our first search engine and let's go first execute it as I had told you without macros how it works first look we are going to go to the data tab we are going to go to each one where it says sort and filter and in advanced we are going to click on it that little window will pop up look here in the list range let's go to select our entire database our entire table since the range of criteria we are going to select look we are going to select all this data but I am forgetting something eye I am forgetting something let's first put the criteria po r example I want it to search for me to show me all the names that start with m I pressed ay integer gay is these two rows are my criteria I want it to show me all the names that start with him so we do the following we go to the tab data we are going to advanced and here look we already have the list range we are going to delete again and we are going to select all these data we select them and the criteria range we are going to put these two here I make sure that it says filter the list without moving to another place and we simply click accept and look look at the filter that I just made according to this criteria look I already have the name with m there I have it ok all the names that start with m if I had put for example here v&v then they would only have shown me maria and bela ok maría vela not only is it the same thing happens with earnings ok so let's go look here data we're going to hit delete delete and we already know how this works no then now yes we are going to execute our macro we are going to record our macro we are going to programmer we are going to record macro and here we are going to put search this is how our macro is going to be called search I click accept I click accept and it is already recording I go to the data tab I go to the data tab and I go to advanced one click on advanced and here in list range I am going to select all this range I select all this range and here below I select everything again is in these two rows of the criteria and I give it to accept I'm going to programmer programmer tab and I'm going to stop recording and now we are going to give it a right click we are going to give it assign macro and here we are going to give it a look let's see look for the macro search we give it accept and look now if I put the my I give it to search notice that the macro is already running if I put it in the v I give it search and look how easy it is look I eliminate this it eliminates the search again it appears if I put it in earnings that is greater than 400 le do and search easily how the brand works that is why macros are wonderful when you know how to use them correctly if we put it here look less than 400 I give it search other data appears ok I delete and search but I wanted to get to this exercise to teach them that macros look macros have events the events are executed they look with click they are executed when they see a change they are executed when you click twice it is executed well you right click etcetera ok that is not only the macro can be executed when you doubt a click on this button the macro can also be executed for example when you click twice when you right click when you press ndr ok these are the different ways to execute a macro and that and all these form all these different ways to execute a macro they are called events because they are called events and we can see the events in the codes ok look, for example I no longer want to use this search button or suddenly I want it I want to leave it ar there I want this macro to be executed automatically every time I write a l automatically l already has to be executed, that is, I no longer need to click on search how do we do that the first thing we have to know what the name of this macro that is being executed the name is right click assign macro is search ogame do you remember or search then I already know what is called and what is called search starts with a capital letter loco control and we go to the events where we see the events in exercise 3 we right click see code on the left here on the left we are going to go exactly to the sheet here look at the sheet where we are running this macro is called exercise 3 and exercise 3 is also here exercise 32 times click twice click here and the events look the events are here but we are going to have to select worksite what does this sheet mean work on this sheet I click work hit and on the right are all the events here look here when the sheet is it is active when there is a change when the sheet is deactivated etc. etc. etc. there are not a lot of events but I am going to select this one that says change this game change in Spanish is change that is, every time a change is noticed it is going to be executed it comes here here is what is up here look at the event here we have it change this is the name change that says no that means every time you notice a change look we just selected words it and on the right we select change and here I put the name of the macro look only the name of the macro that is to be searched for then every time this sheet notices a change the search macro is going to be executed look I am going to put the name I press enter and look it has just been executed here it is already executed I eliminated look i am executed again if I put the executor mind eliminated the m it executed me if I put here more than 50 enter it executed me if I put less than 100 esther it is already executing me that way I avoid using the search button but yes i eliminated look, it executes me, so we just executed this this macro with the event with the chang event, which is change, no, that is, every time it noticed a change, the search macro is going to be executed, let's go with the last macro, look, it is to automatically order while write ok I'm going to select all this I'm going to start I'm going to put a little board and I'm going to change its color I'm going to make it bold now yes ok I want first we're going to execute it with a tab button insert shapes we're going to put a little button right click edit text order first with a vote ok and then we do it while typing order there we have it good we are going to put we are going to select all insert tab and we are going to give it a table this again I tell you we put it in table mode in case you want add more rows more records and the macro is always executed so you create the data so that it takes into account the new data we click accept and that's it ok we are going to put it in bold so that this can be seen better per don sorry bold there we have it then I'm going to delete well I'm going to put it here then I'm going to put the center there we have it we're going to record our macro we're going to give it programmed we're going to give it record macro and here and here we're going to put sort this is what our macro is called sort data look like this I am putting sort data we hit accept we are going to select all our table first we select all our table we go back to the data tab data tab and the macro is already running and here in sort look we are going to order that the question is how do you want to order no I am going to order it by earnings because earnings are numbers and I am going to show you from lowest to highest ok so that the ones with the lowest number are shown first and the last one those of greater I do not give a click less less less to greater I accept and nothing else I go to the programmer tab and stop my recording now if I come here right click assign macro and we will give it to see order here until order d atos was ok to order data we put not to order data and we give him accept I'm going to put a new record I'm going to put the uncle's surname you can put it tec and look here I'm going to put the number 5 which is the youngest not because the youngest it was 10 before I adore 5 if I give him order look if I give him order a click and now look the tec guy is already at the top no but I want to order as long as I type so as not to be clicking then don't forget that he exists change event which is every time you notice a change don't forget that the changes are when you delete when you press center when you write those are changes any simple change then the event will execute the macro that you tell it what the macro is called right click assign macro the macro is called sort data we are going to copy it now my macro is called sort data and I no longer want it to be executed with this vote if I do not want it to sort the data automatically when I am writing then we already know that e there is the side in exercise 4 right click see code and the exercise look we are going to go here exercise 4 here ok here is the leaf it is the name in parentheses of the name of the eye twice click and we already know that it is called that we are going to select words it to work on this sheet and here we are going to select the event change the event if not there we have it and here we are going to put the name of our beloved macro called sort data and we paste it min the one below it doesn't matter no matter you can delete it there is no problem but look now if it is already in the middle of the cheikh event and what happens now we are going to close this what happens now if I added a new record look I am going to put a lot here let's see let 's see I'm going to put candle and here I'm going to put number one it should go up automatically one pressed enter and look automatically and he just ordered me and fought candle which is the record that I just put it already appears so we can understand that macros can optimize this type of tasks and just like these exercises there are thousands and thousands of exercises that you yourself can create for your convenience so that you are not performing tasks, you could say every time and in the tabs by ordering two filters etc. macros no se olviden que siempre te van a automatizar tus tareas ok te va a ahorrar muchísimo tiempo si sabes cómo utilizarlos yo me voy a quedar hasta acá si tienen alguna duda alguna pregunta pueden comentar este mismo vídeo para ello responderles no se olviden de suscribirse a su canal el Tio Tech y nos vemos hasta la próxima
