PART- 18 – VBA Charts and Macro Recorder
Transcript For Video – VBA Charts and Macro Recorder
Video on VBA Charts and Macro Recorder – So let us start the recording the Macro this is the range on which we want to actually create a chart so I have selected that range first for creating a chart I need to go into the insert Tab and from here there are various option of charts which are available I guess for the kind of data that we have we can actually look at probably column chart so let’s choose the column chart and click Ok or probably just live it here and you will find that chart automatically gets populated now if he look at to chart very closely you will find that series 1 is not making any sense we can kind delete this please not again that whatever we are doing here this is being recorded via Macro so an automatic code is being generated for any activity that we do here so do only those activity which you think are important and you may want to capture that at the back hand through we can always go back and customize and remove unnecessary code which were develop but you know the let us limit that to the possible extend so let me colored this may be Orange for just a sample case I may want to also add the labels here so I’ll add labels I may want to delete the gridlines I can also do that so if you look at this graph may be one think is clearly missing which is the chart title so let’s add the chart title here by going to the layout and add chart tittle and may be above the chart so let’s write Google Valuations chart tittle another think which is kind of missing could be a some think like the access so here we can also add access let say he vertical access Rotated one we can write this as fair value see the reason I am trying to you know a do many think here in the chart itself is that I want the code behind this so that you know it becomes easy for us to kind of logically connect so in order to generate this code only I am actually doing this thinks may be you may have better way to look at all this without the macro as well so any ways we will continue and any other kind of formatting if you can think of you can still work around ok so let’s stop the macro here and see what is the exact code for whatever we did now ok so here we had the macro and we see that there is sample chart, chart sample were created let’s go into the edit and see what has been the overall code so if you look at the code behind it looks very very difficult and confusing to honest but if I kind of break down and remove the unnecessary aspect of this charts probably you will find it to be very very simple so let’s start from the top let’s analyze this what this means range A : B8 so here what we have done is basically we are talking about this range so you are selecting this range then you have active sheet. Shapes. Add chart. Select that’s what we have done here ok now you want to kind of make column clustered chart so for that active chart make the column clustered chart then source is actually given here so likewise you will find that we want to delete the legent so I had on the right hand side there was series 1 I wanted to delete that so that gets deleted again if you look at we wanted to delete the gridlines we have also done that, we wanted to add the labels on the charts we had done that so likewise this code in between this codes add the chart title Google Valuations now anything else just code looks like I had kind of clicked on chart itself so it is just providing me with kind of format that that is available so this code looks like is not so important here so we may delete the code between this and this what else do we actually went ahead and kind of change the access title so that what we have it here as fair value so we did something on the top so remember we had done within the layout here in the access so whatever activity we were doing here is actually being captured in these set off codes let say the we when back to the chart most case I may have you know went back and click here so this is whole codes comes again so may not be required so let me do one think I’ll just put some explanation as well need way to look at this is that if you note that this samples which are written above these are green color, these are nothing but comment comments and these comments are not executed they don’t get executed at all so what I’ll do is I’ll just write this is in the common format write in order to common format you just need to type this and write whatever you want.