Updated June 12, 2023
Part – 1 – Excel VBA macro
Transcript For Video – Excel VBA macro
video on Excel VBA macro – Hello so let’s start and create are first Macro and Automate a basic task so a before we do that let us understand couple of protocols while we talk about Macros current version of excel which I am looking at is Microsoft excel 2010 and I am assuming that you may have this but if you don’t are you currently working on 2013 awesome but if you are working on 2007 that would be good enough so though nothing much changed from 2003 as well but we will recommend that either we use 2007, 10, 13 for going through this tutorials now that is no 1, No. 2 is when look at the top hand corner left hand corner you will find that there is home, insert, page layout these are called as Ribbons for accessing Macros and doing stuff within Macro you will require tab here which is called as Developer Tab these tabs are not present by default and hence you must get access to this by clicking here on file, click here on options and you have to kind of customize this ribbons so this is basically top called as ribbons and you need to customize a ribbon so please look at the right hand side where it is written as main tabs and you will note that you know there is one which is called as Developer Ribbon has been un check if you may have to go and check this ribbon if it is not present in your case so I have checked marked that and I am pressing Ok so movement I do that you will find that developer tab that gets build up and this is where I can access my macros so you can see that under this tab there are various information which is given different kind of icons, visual basic, Macros, record Macros, Macro security add inch so probably you know we will go through all of this one by one as and when it is need it for the time being please understand that this is the place where we will create our Macros and Automate tasks using this tab. So let us now try and do a quick exercise very simple one and see how this automation are possible so exercise would be something like this why don’t you type Corporate Bridge and you know give it font size of may be 16 and bold it and that’s about and may be you know let say color this in red so what we have to do, you have to create Macro which not only rights Corporate Bridge but also convert this into a font size of 16 bold it as well as colored them in red color so this is the kind of exercise that we have so let’s try and see how this works so for doing that you know since I am assuming that you are looking at this for the first time the place where you will find this things to be done is the Developer Tab so here from the Developer Tab you need to click here on Record Macros so as you can see I mean each of the command perform will be saved in the Macro so that you can play back again so we talking about automation here so let’s click here and see what comes in this case, so as we may have noted here the dialog box opens up this dialog box contains couple of entries to be inputted No.1 is the Macro name by default you can see that the Macro name is Macro1 however you can choose your own set of you know customized the name as per you macro so let me kind of put this as automation for the time being now this word can actually specify that I am trying to automate the process of formatting the word called Corporate Bridge so just taken in random way however one think very important from the point of view of macro name is that you are not allowed to actually have spaces between the names say for example if I would have used automations space corporate space bridge and had I try to execute this command it prompts with an error and it says that reasons could be many but one of them is name contains space or other invalid characters so what this mean is that a space for macro name is not allowed 2nd is that you know you will find that there are other reason as well it is important to note all this thinks because you are doing is first time and you may find yourself into unnecessary situation if you don’t to this basic rule so name does not contain with letter or an underscore and basically we are not allowed to have kind of character in between as well so let me kind of delete this words Corporate Bridge for the time being and keep the Macro name as just automation on the 2nd very important activity is the shortcut key so instead of you know calling the macro from the menu command I’ll show you how it can be done but you can also make use of the shortcuts so you can actually define shortcuts for your macro so I am sure you may have heard about CRT C which was for copy and CTRL X for cut and CRTL V for Paste so we know that these are well known you know shortcut keys likewise you can define your own customize shortcut key for this macro so let me use this Q as the shortcut key see I may kind of not use the regular you know shortcut like CTRL C or CTRL V because you know I may not want to interfere with you know regular set of shortcut because at the end of the day these Macro will actually overwrite the actual shortcuts provided by the excel so say for example this was named by CTRL C this Macro would have been executed instead of the well know function of copy so an another important thing here is that excel actually differentiate between the capital C and small C a small C is basically CRTL + C and if you using capital C you can see the shortcut actually changes to CTRL + Shift + C + which is in your keyboard so there is clear differentiate between 2 so you may find yourself in situation macros not running because you pressing CTRL + C however it was actually defined as capital C which means you need to press CTRL + Shift + C from your keyboard ok so I’ll probably you know keep this as Q because probably we may have not have used Q as some well known shortcuts ok so let’s move to the 3rd one which is Store Macros in there are 3 options actually available here 1 is workbook the new work book and the personal Macro workbook these work book essentially means that you want to stored the macro here in this workbook and you will be executing this macro in this local work book only in this work book your macro will get executed if I open a new work sheet or sorry new book all together this is book 1 so if I open let say book 2 my Macro CTRL Q will not work ok now there is another option called new work book, new work book essentially means that you may want to kind of stored this Macro in a new work book and kind of run this Macro in Multiple sheets and process could be like if he choose new work book CTRL Q and the Macro name automation will get saved in this new work book you may have 10 worksheets or 10 work book 10 different work book for which you want to run this command called CTRL Q idea here is that one you open the new work book you will be able to open other set of work book in the same instead and execute the same Macro probably this comes at slightly advance stages for the time being will be concern with this work book even the personal Macro workbook is beyond the new work book as such you know when you talk about personal menu commands, personal Macros, customize Macros in a single work book and you want to use it multiple work book different places you want to email it your clients so may be do your within the firm you know probably you may want to use personal Macro workbook which he has high level of customization so this all comes in actually the advanced macros but for the time being we will actually you know deal with this work book as such ok. What about description, description again if you are fencing loot of macros, let’s say you know you have 100 Macros in this workbook obviously, it makes sense of you to kind of have some description associated with each macro otherwise, you may lose touch about which Macro was for which propose all together it’s good to have description so let me write description so let me write description here that you know formats Text that set I think you know that should be fine and you just need to press ok so this is how you are macro modalities are completed now the movement I press ok look at the top left-hand corner it says stop recording that means you know you are recording is actually started so whatever I do now this will get recorded as an instants which will enable way to do automation.
