Excel VBA Tips n Tricks #21 Control Charts with VBA

hey welcome back everybody thank you for tuning in this is Dan strong with excel vba is fun today I'm going to show you a quick trick some basic tips about when you were making let's say you're making a report and it generates and sometimes it generates you know this many sometimes it's this long sometimes maybe it's just a tiny report but we're talking about a changing number of records and you want a a chart to dynamically attach on to that maybe at the bottom of the sheet maybe on the side or wherever you want it to and however big you want it and all these different attributes can be changed using visual basic so you can have it dynamically appear each and every time the way you want it to so without further ado I'll just make one more record here from the east region will have Jeb and he made a whopping five thousand dollars in sales I'm going to highlight column C ctrl shift four that makes it all a dollar sign makes it nice and pretty what we're going to do first of all I always recommend and advocate go into the developer ribbon and recording a macro in this case I'll just I'm not going to use relative references I think I was doing that for something else earlier go ahead and record a macro and what we're going to do we'll call this sample chart alright and now we're going to go to data ribbon excuse me insert well just insert a regular typical clustered column chart and there we have it it's changed a few features here let's go ahead and plop in a special thing here and we'll click on the the legend and we'll hit the delete button just let's just kind of review what all happened just now we clicked on anyway the region basically that we wanted then we inserted a clustered chart and we took away the the title there the legend and we still have it activated if we click away would become inactive we could even relocate it and even resize it like so let's see let's read up on what we just did so go to the Developer tab click stop recording and we'll hit alt f11 and see what just happened so we have oh let's see here let's look for that most recent module would probably be I'll have to do some looking sample chart okay if we found it it was hiding in module 12 for whatever reason sample chart sample chart here we go here's what happened first on the active sheet they we add we generated a new new chart and selected it from the shapes object here then the same thing the active chart they knows these mostly start with active chart so they're just taking on whatever chart you got selected at the current time so the chart type with B K we was at excel column clustered so there must be a list of different types of charts that you can have and make that change to active chart dot sent source data equals and so the source is anything beyond the this with a colon and equals so we had the range ignore this part of your chart one is actually the name that I gave this sheet it's kind of misleading that's nothing to do with a specific chart so the range was this worksheet and a1 through c5 and you can see that that is actually our data range here so that's good then the chart style became 44 by default I believe it was to to is just the kind of ugly one and 44 is this nice pretty black one with the red with the red columns then we selected the legend and we deleted it so selection delete imagine we could also do just dot legend delete if we wanted to skip that extra line then active sheet chart object dot this and this is chart for that was the name of this chart dot activate so that Meza man we clicked somewhere on here we activated it and we can see the name is actually charm for so we're on the right path then we actually moved it we incremented to the left of negative 57 0.75 and we incremented the top was negative 130 s that means we pushed it upward instead of taking it downwards it's kind of like an offset then we clicked on it again that's this activate here then the scale with we actually mess with the width and the height so that's pretty interesting alright so a few things right off the bat we want to do with this is we want to be able to dynamically make this chart show up or fit into a certain range I think that's important to do especially if you have the last cell is on row five last rows on row five but maybe tomorrow is row 10 or whatever when you generate this report so it's important to say that you want to chart to start maybe a couple cells down and then be like this big so you could actually set the range from here to here in fact let's mess around with that a little bit we want it to fit within that so there's a way to do that let's go ahead and I'm going to go ahead and delete this chart and we'll go ahead and start from scratch using kind of what we recorded so we will go with we're not going to use active sheet because we want to be able to well you can generate this using other while on another sheet of one this is invisible but activating it I think you do have to open this sheet even if it's first split second so I'm gonna hit f8 f8 active sheet shapes add chart dot select so that adds a chart and selects it you can't probably see that I'll scooch it up here a little bit so there's our default chart it hasn't got any data in it the tarp type is now going to be column clustered and that is the default anyways the source data now this could be dynamic here's how let's go ahead and get our last row if you can watch my videos you know that you can get the last row very simply just memorize this dynamic bit of code here so we're going to the last row it is going to be I'm not even going to declare the worksheet we're just going to go for it active sheet that cells rows dot count comma one dot end clips Excel up row so that's going to be our last row and we're going to actually have this source range be dynamic so let's say CH T range chart range equals um cells and we'll take LR excuse me let's use the range object here the range of a it's always going to be a 1 through a C and we'll join that with whatever row it is which is LR so there is your chart range range a 1 through C and let's see what LR is we know that it's 5 let's look ok yes it is 5 so a 1 through C 5 is now going to be set to chart range CHT range so now we could change this and make it dynamic by saying CH trng the source is always going to be this dynamic thing we just we just declared but we just accidentally made another one okay that's fine callin closer the source is going to be with that dynamic range we need to set an object I'm sorry set chart range okay sometimes they're weird about objects and everything so let's do this again I'm going to delete this delete this and let's go back to our visual basic editor okay here we go fa fa okay so we've made a new chart the chart type is a cluster now it's going to be this object called chart range and sure enough it took on that data so I'm going to go ahead and manually scooch it up here let's say that we wanted to use the last row plus two or three lows plus three okay so it'll be on row eight that's going to be our starting row four we'll call it a chart area is going to be equal to okay chart area is going to be equal to the range of about a and what's the what's the row that we want to use is L R plus 3 and we'll join that with through a C and we're going to join that with how big do we want the chart to be maybe 8 8 through C or how about a a through D 13 so 8 plus 5 oh pardon my math here I'm having a brain flop here so we're going to have to make this D and L R Plus let's just make it 9 and we will cover that up here so it's going to be a and so a 8 through D and L R plus 9 which is d 14 here so let's see what that does that's going to be our chart area let's go back and establish that that chart area is that we may need to set it I'm not sure let's see here hmm excuse me so here's how we're going to here's how we're going to do this we can get the parent object from the from the chart so I'm going to show you how to do that here okay so what we need to do I'm going to back up a little bit oops I want to do that I'm going to set the chart area to be equal to this and then I'm going to set the the chart object to be equal to active chart dot parent that's what you need to do and we may have to declare that chart object as a chart object but let's see if we give I would not do that what you need to do so each range selected highlighted range whatever has a left and a top and the height and a width so we're going to take those attributes from this chart area on our worksheet which is a a through D 14 we're going to take the the height and the top and the left and the and the width from that and make that the charts top left width and height and it will actually move the chart to whatever dynamic area that you say so we're going to say we're going to take this CHT the chart object . let's say that top equals ad chart area dot top all right and we're going to go ahead and go back and let these be set as object whenever you put set it makes it into a an object that Excel can understand so heck with it I'm going to go ahead and delete this and now that we've got some more things to work on here we can we can go ahead formulate that here after charm and there's the active data that we need now we have our chart here we're going to see if this works here the chart object top equals the chart area down top let's try that oh we have a error all right let's go back to the top here we're going to declare a CH t obj as chart object yes that's okay if it resets so we're going to we need to set that up and declare that correctly let's see what that does for us hmm okay I'm having another really terrible moment there brain fart as I call them I'm gonna uh how about we set up this object after the active chart actually exists huh all right let's let's move back here we've got our area set up active sheet okay so we made that made the chart now and we're going to set that chart object to be equal to the active chart parent and now we have chart object setup the top is currently one eighty one point eight whatever but we want it to be equal to the chart areas top so now that will move and let's copy and paste this line here clips to me all right now we're going to take the left of each we're going to take the height and of of this and make it equal to the height of the chart and of course the width of each all right so let's back it up a little bit where did the top let's get the left of the chart area currently we're at two ninety two point five now we're at zero right against the wall okay and now check this out we're going at the the height of from a eight thirty whatever we're going to get the height of that and make it at the height of the chart and we're going to get the width of from here to here and you'll see that it'll squish it down and maybe we don't like that so much maybe we want to make it the chart equal to a eight through F fifteen or something like this so let's make it a two through F and 15 so it would be lr+ 10 and that will get a little more wiggle room so let's go ahead and delete that and we'll redo our macro from scratch hit f8 all right so we're setting the chart range chart area now and chart areas a little bit more comfy a little roomier now so as we readjust this thing here we're going to set it up here okay the top left height and width it's a little more comfortable I would say make it maybe lr+ plus fifteen is that overkill let's try that again I'll just put a stock mucker right here I'll delete this chart Altaf eleven all right now that we're here I'm gonna hit f5 and it will stop right here after doing all that stuff hit f5 so now we have a little bit more comfortable of a chart here and you notice it is a 8 through F 20 exactly sized with however you know where we wanted it sized if you wanted it off to the side just just adjust your your variables there when you're setting your chart area but that's pretty neat pretty neat trick and you can keep on with whatever was already on there we want the chart style to be equal to 44 make it all pretty and we want to select like the legend and to delete that in my case I do anyways also this stuff yeah I don't care about that stuff but now we have something dynamic so just to show you what we did there in fact let's make the let's make it interesting let's make the chart area see whatever through H whatever so that I'm actually scooch it to about like this just for kicks okay so I'm going to I'm going to select the chart and delete it and I'm going to make sure that I end that macro now from scratch let's go ahead and let's hit alt f8 and let's find that one that was called uh let's see CH plus B's or if we had it's assigned to a key wouldn't it what we call this one chart this is ridiculous okay so it's trying to get trying to get my macros from my other workbook that I stupidly have open right now this one is called sample chart whoops okay so I'm going to hit f5 it's going to do all those commands at once for whatever size this chart is so I'm going to hit f8 errors f5 and oops I put my start marker there let's see f5 there it is BAM it's done now let's see what would happen if I have a few more entries Sharon is from the north east eastern region and she has sold 4,500 and finally a Billy Bob is from South and he has sold 10,000 so he's going to show up loud and clear on there on the chart okay so I'm going to hit alt f8 and see if I can find starts with an F or excuse me an S and it's called sample seriously sample chart here it is I'm going to run this one and I'm letting off the mouse now and wow there's a dynamic chart it's just like that how I guess then you can play around with maybe recording it go into design mode and tweak some things and just see what it says and the outcome of each recording until you kind of until you really like how it looks and then just take those and glean whatever settings that you enjoyed from that you may have wanted to be able to change that chart to a different type of chart or you wanted to format the words into a word artist something you could check all any any and all features that you see there you can use so very powerful in fact I kind of like that I wouldn't mind it being a little bigger if that was a big feature of my report here so anyway thank you for watching God bless

Channel: ExcelVbaIsFun

Views: 60,641

Rating: 4.7919998 out of 5

Keywords: Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, chart, charting, excel chart, vba chart, charts in vba excel, charts

Id: fNEeQIDQxII

Length: 20min 44sec (1244 seconds)

Published: Sat Apr 27 2013