Updated June 12, 2023
Part – 2 – Excel Macro
Transcript for Video – Excel Macro
Video on Excel Macro – We are basically looking at Macro that can write corporate Bridge make it as font of 16 Bold it as well as color it in Red so that what we will do so let’s choose the cell A1 I’ll write Corporate Bridge I go back to Corporate Bridge and got to home tab I color this in red font size was 16 and then I’ll bold it so I’ll bold it using the Menu command that’s whole macro should ideally do so I’ll go back Developer tab as it started there is stop recording button here as well so I must stop it so that’s the recording of macro is stopped completely now one very much important think that you must be aware of is that when you start the recording of the Macro anything which goes on screen as function typically recorded as part of the macro so lets if you were wanting around here and there probably you know doing some other set of calculation and then you came back to this and probably road Corporate Bridge you know all this instances will get recoded so we may call this as noises when you are kind of you know making Macro please ensure that you stick to what your core objective is and you know you can definitely remove the noises later but sticking it to the core actually helps the purpose and the it fasting the process of you know creating a macro so let’s now look at and now see Macro actually works for ask or not you remember we had defined the shortcut as CTRL and Q so let me see if CTRL Q works in our case so I have select this cell and I am typing CRTL Q so I see that we have type Corporate Bridge but probably the formatting which we wanted 16 font red color and bold unfortunately is not happening here let me try it again CRTL Q in this active cell I guess what has happened is probably we have missed a think and I am sure that like what we saw in our case if you are trying at your home you will find that you may also have been prawn to this kind of error kind of unknowingly now in order to resolve this I mean since this macro has been recorded and probably is functioning in a way it was desired to what we will kind of do is that we will look at how exactly I Macro actually work so we will actually go back and look at the anatomy of this Macro and probably tweak some things here and there and this will also help us understand procedure that we are going to adopt to kind of you know create our automation coded so let’s click here on Macros what I am trying to do is that since is Macro recording the desired result incorrectly I would like to go and see the source code and probably understand from there what went wrong and if I am able to kind of correct that in the source code itself so that why In order to access the macros which have been recorded to you need click on Macros here or ALT F8 is the shortcut and you can see that this is macro which we had recorded if you had kind of you know 10 macros here you would see the name of 10 macros however we have just recorded only 1 Macro so that why the word automation comes here so you can see there was various option which are available run so you can run your macro from this window as well or CRTL Q which we had defined as the shortcut don’t worry about the other set up options the one which I am kind of interested in is the edit so click on edit and you will be taken to place were probably it’s look unfamiliar to start with because you know this is something called visual basis for application basically you know this is the editor which is used to modify the code and write the code so don’t worry about you now all this layout and you know non finalization of this whole tool at this stage let’s look at this part because this is what the Macro is all about this is sub automation so think about this as macro and sub essentially mean Sub routine I mean this is more from jargon so don’t worry about that this sub routine or process or application which we had design and it also ends, ends Sub routine so it’ starts and it ends now don’t worry about you know so many think which are really unfamiliar at this stage here we are kind of looking at a problem at hand where you know color codding is not being transferred to the place of the active cell now think about these which are green as are comments and the name is basically automation macro and you remember the description was formatting the text and keyword shortcut is CTRL Q so this is just but information of the Macro now let’s read through the macro code and kind of understand what is going on it says active cells formula R1 C1 = Corporate Bridge it says nothing you know but since we had selected on the cell in written Corporate Bridge just means that Ok that talking active cell then what happens is it says that Ok range A1. Select A1. Select so let me actually go back to mu excel sheet and what this cell is all about this cell is A1 so it essentially says select A1 cell ok I can go back to my source code by pressing ALT F11 here or this icon here so I am then asking excel to actually go to cell A1 now with this selection color this in Bla …. Bla … you there are other activity here which selection is not font makes it as size 16 which we actually wanted and you know probably this code looks totally unfamiliar to me but the one which is off interest is basically that I am instructing my macro to actually select A1 as cell now on 1 side I was expecting the result on the active cell, active cell is nothing but the cell which I am kind of placing the cursor on you know hoping this should this is place were red color and other font activity should happened with the corporate Bridge name the but only thing that happen when I execute this command is the word Corporate bridge is written and then the remaining activity actually happens in the cell A1 so I’ll go back to my visual basic editor and probably you may have figure doubt that I must not go back to cell A1 I should stick to the active cell. formula.R1 C1, whichever place I have selected so I’ll just go here and delete this range A1. Select and that it I am not going to change any other codding I am just probably you know interfering here and you know stopping this and going back my excel and hoping that this macro will work now so I’ll press CTRL Q and wow so lets see you know if it works across the doming so here you go so you have actually seen an example where you are understanding of the macros was limited but you were able to kind of go back into the visual basic editor and probably understand some code because of intuitive nature and did some thinks logically so here the macro actually run beautifully and congratulation if you are able to kind of run this macro congratulation you were able to do that for the first time so we hope to continue and right many such macro in much more sophisticated and meaning full way please remember the key take it away from this whole activity was that we are not coders so we will not be coding purse we could have actually coded this from scratch but what did you do we actually started by recording a macro we take we took use of excel automatic codding language and you know it help us do some specific codes and so at the end of day we just looked at logical codes and manipulated so that what we are going to do so we are not coders we will just look at the logical code and manipulated as per our needs so that’s how it is going to work in the next set of lecture.
