Excel VBA Introduction Part 50.1 - Introduction to Charts in VBA

welcome to this wise L excel vba tutorial this video gives you an introduction to working with charts in Excel VBA so the video is really all about the basics of creating editing and formatting charts and we'll begin with the basics of creating a new chart and choosing where that chart gets inserted into your workbook such separate chart sheet will then look at various techniques for selecting data for the charts from very basic static cell selections through to more dynamic things including if statements to control exactly which data gets inserted into the chart once the charts been created we'll look at how you can modify the source data and also choose a different chart type if you don't like the default one well spend a little bit of time looking at how you can control which chart elements get displayed so that includes things like the chart title and the axis labels and data labels etc then with the final part the video covers a range of different techniques for modifying the appearance of the chart so it includes using the built-in chart layouts chart colors and chart styles and then finally how you can create and apply your own custom chart templates for a completely customized look and feel for the chart so it's a fairly basic introduction but quite a lot to do let's get started okay so the starting point for this video is a workbook with a few worksheets where the date when it I've just built up a few simple sheets here a couple with academy-award information to Oscar winds for a few films in 2015 and films in 2016 and another one showing the list of the highest-grossing films so it's a top 50 highest-grossing films of all time apparently I've done a little bit of consolidation there as well to work out how many films there are in each year and the total and average from the films in that list and I'll get a link up to download this file as soon as you can otherwise our website and I'll stick the link in the description below the video if you're watching this on YouTube just in case you can't wait that long and I will try and do that as quickly as possible this sort of date is fairly easy to get hold of with a quick bit of googling you can find on Wikipedia most of this information so there's a list of the Academy award-winning films broken down by year it's not exactly what I've got a sub slightly changed the the arrangement of data in the worksheets but you could easily just copy and paste this sort of information onto your into a new blank Excel workbook and the list of highest-grossing films is there on what your beauty there as well so that's just pretty much a straight copy-paste in a little bit of formatting to tidy it up so depending on what you want to do if you've got the data already if you've downloaded it from the wise our website or you've just copied and pasted from Wikipedia the starting point for us is to head into the developer tab and the visual basic editor and we're getting it started by writing some code to create some charts so we'll need a module of course to write some coach let's just insert a new module in the usual way and I'm not gonna bother renaming the module it's worthwhile pointing out there that I have renamed pretty much all the worksheets in here so I've given code names to each sheet so if you haven't done that already in your own work box if you've copied and pasted their data from Wikipedia then I've given names to the worksheets like WS money WS menu etc etc so you can change the code name just in the same way you change the names of modules in the properties window anyway in this module one that we've got so the first ability we're going to write is going to be called create some charts the amount of code that you actually need to create a chart is pretty minimal all you need to do is refer to the charts collection and then apply the add method to it generally is all you actually need to do now exactly what will happen when you execute this will depend on exactly which cells you've got selected in the worksheet so I'm just going to restore down my VB editor window and make sure that I'm on a worksheet that's got some in Daytona so I've got cell a3 selected in the awards 2015 sheet what that means it's when I run this code if I've got a cell selected in a region of data then what Excel will do is pick the entire region and generate a chart based on that so we'll actually select the cells but that's the region of cells it will use to create the chart so just to prove that if I quickly click back into the visual basic editor and then just run that subroutine I will end up with the chart that is showing all of the films from that's 2015 Academy Awards list so everything from Americans my bed down to whiplash now of course you can't always rely on the fact that there will always be a valid cell selected to create your chart so it would make sense to write some code to select the correct cells in the first place so what we could do in this case is write some code this selects the awards 2015 sheet and then select cell a3 and then creates the chart so that's not too difficult to do we've done stuff like that many times in previous videos it just means adding an extra couple of lines of code at the top of the procedure so using the coding to the worksheets if I press ctrl space on the keyboard I can quickly find my WS or world's 15 worksheet and say dot select and then say range a3 dot select now really doesn't matter that I select cell a3 here it really doesn't I could select any single cell in that table of data the key is if I select a cell in there and I press ctrl a on the keyboard then the region of cells that get selected that's what Excel will base the charts on so having done that if I just go back to let's see let's go back to chart 1 in fact and then if I go back to my VB editor and I run this subroutine again it will go to the correct worksheet first and then very quickly generate a chart based on that so I get exactly the same data now it's not too difficult here to just modify the name of the work Keith I select so change that to do the S Awards 16 which is the code name for one of the other sheets and then just do exactly the same thing again I'll get you charge but this time based on data from sheet or from the Academy Awards 2016 instead so you can see I've got everything from best story there to Straight Outta Compton master list of films in 2016 now as you can imagine in this workbook we're going to be generating a lot of charts throughout the course of the video so it probably makes sense at this point just to write a quick subroutine to help us quickly tidy up so what I'm going to do is write a subroutine that deletes every single chart from the entire workbook so to do that back in the visual basic editor just to make this easy to find I'm going to add this one right to the very top of the module I'm going to call it sub delete all charts and all we're going to do in here in a similar way to the way we created a chart very quickly and easily if I simply refer to the entire charts collection I can then apply the delete method to it that would attempt to delete every single separate chart sheet that's in the workbook now just as it would occur if you manually deleted a chart from Excel view I tried to right-click on chart 3 say and delete it it warns me that I need to do that so I'd have to confirm that I wanted to delete it and rather than having that message pop up for every chart that I want to delete what I'm going to do instead is turn off those alert messages so before I attempt to delete the charts I'm going to say application not display alerts equals false and having done that if I were to run the subroutine right now I would find that all the charts I've just created have now disappeared now it's worth mentioning that if you don't have any charts to be deleted then trying to run this ability in again will cause a runtime error so you can't delete something which isn't actually there of course so it might make sense again if I just hit the end button here to add a little bit of error handling just a kind of really really basic error handling I'm just going to say on error resume next on that just above the line on which I attempted to delete the charts collection now we've created videos that go into handling in a lot more detail than that that's one of the things you can't often get away with just ignoring runtime errors but in this case it's perfect if I can't delete the charts then they don't exist in the first place so I don't need to delete them so I can just happily ignore that line and now I can happily run this subroutine as many times as I like even if I've created a few charts if I run my create some charts routine a couple of times and then go back to delete all charts and then that one it doesn't matter how many times I choose to run that one it won't throw any runtime errors okay so back to creating some charts we've seen that when you pick a single cell in a list that Excel just uses the entire region of data to create the chart what if you wanted to pick out some specific rows so let's imagine it was a nice single straightforward block of cells let's say I wanted to create a chart based on and let's say the first five films in a list so that's everything from cell a3 to include the tiles down to cells c8 so if I wanted to do that I could make sure that I selected that region of cells first so back into the VB editor again if I simply change my selection there from a 3 to a 3 to c8 then if I select that range of cells when I run this ability now I'll get a chart that's based on just those five rows so if you select a single cell it uses the entire region you'll be select a block of cells then it will use just the selected cells to create the chart now out of the arrangement of cells you wanted to base your chart on was slightly more complex so let's say for instance you wanted to create a chart that didn't include the Oscar nominations it was just the Oscar wins so we wanted to select everything from a three down to a twenty four and then C 3 down to C 24 in this particular worksheet so to make that work what we could do is if we head back into the visual basic editor let's just modify the existing subroutine that we've got here rather than just writing another one so to start with I could say a 3-2 a 24 now what I can then do in a single set of double quotes I can write a comma separated list off cell references this is perfect as long as you know the range of cell references you want to include so after the comma I can then say see three columns C 24 and then if I were to run that subroutine at that point I should find that I end up with a chart that only includes information about the Oscar wins not the Oscar nominations it's just as simple although slightly more time-consuming to do that for individual rows as well so if we went back to let's say let's go to all 2015 this time let's say I wanted to include information about just the good films from this list let's say all the ones that I like let's put it that way so I could go up to the VB editor and let's just change the sheet that I'm selecting to Awards 15 and then I'll start by including the column headers so I'm gonna go for a 3 to c3 first of all so that's the first book a 3 2 C 3 followed by a comma and then let's go for first one's gonna be guardians of the galaxy last best film at least I reckon I lutely adored that film so let's say 8 8 2 C 8 and then let's have another comma and let's have what else do we have instead I quite enjoyed that one as well so let's have a 11 - c11 and then let's go for another couple ones let's go for the imitation game actually we saw that recently but I thought was great a 16 to see 16 I haven't seen the theory of everything but I think that could be quite good as well but I've actually watched it yet so and let's just go with that I've got one row titles and then three rows of films so if I've done that and I run the subroutine again I alternate with another chart that just contains information about both columns of us travesty their Guardians didn't win any Oscars but there we go and then the three row today that we selected so you've got quite a lot of power as long as you know the cell references it's relatively straightforward to include just the specific cells you want okay so what if you wanted things to be a little more dynamic than what we've done so far if I head back to Awards 2015 what if we weren't sure how many rows of data there were in the list and I only wanted to include let's say cell a3 down to column the bottom row in column b so everything from a 3 in this case down to be 19 but I wouldn't know which cells at the bottom of column B so we can use some standard sort of selection tricks we've covered these in previous videos in the series but if I wanted to select everything from cell a3 which will be the top left to cell b3 dot end Excel down that would form the block of cells that I'd use to create my child from so just to demonstrate how that works in practice let's have a quick go back to our so but in create some charts and I'll go from cell a3 so the I'll change this so it says range a3 pull by a comma and then I'm going to say range B 3 men 2 B 3 B 1 range B 3 dot and Excel down now talk to an awful lot in previous videos about various ways to find the end of a list in fact there's a recent video I think it's number 45 ish in the series about how to find the end of a list so I'm not going to go into too much more detail about that right now so if I've done that and I just make sure that I haven't gotten that block of cells let's start with just to prove that it is doing what I want and then run that one again I'll end up with all the rows in in the 2015 worksheet but only from the first columns are just the nominations column rather than the winds and just to prove that there they are okay so what if things were a bit more complicated yes again let's say for instance this time I didn't know how long the list of films was but I wanted to include everything from column a and column C but not call them B that's a little bit more tricky but still well within the realms of possibility so let's head back into the VB editor and this time I'm going to do is use the Union method to effectively join together a range of non contiguous cells to form a single contiguous range so in front of the word range here in where I've said range a3 I'm going to start by typing in the word Union to refer to the Union method then open some parentheses and if you have them use this before basically all you need to do here is specify up to 30 different ranges of cells that will be joined together into a single range so the first range that I'm going to specify here is everything from a3 down to the end of the list so I just need to change b3 here to a3 so I'm just going to close an extra set of parentheses there at the end as well just to wrap up the Union method and then essentially that little bit of code there refers to everything from a3 down to the end of column a now all I need to do is add in another parameter or another arguments of the Union method that will do the same thing for column C so while I've got this bit selected I'm going to copy that to the clipboard ctrl C and then in between those last two close parentheses at the end I can type in a comma and then paste them will have just copied with the control V and then change this so it's range C 3 2 C 3 end Excel down I'll maybe just separate these things out onto different lines just to make this a little more readable so he leaves the continuation character space underscore and another space underscore here just so that hopefully you can see things a little more clearly that just shows you which block of cells we're going to select so having done that if I run this one one more time I ought to end up with this time the Oscar winners or the wins column rather than the nominations column for all the films in that table in 2015 and just to prove that this will work regardless of which worksheet we're selecting because our worksheets are set up pretty similarly and 2015 and 2016 everything starts and ends in the same place if I were to go to my BBA later and change this to Awards 16 and run the site again then I get to the Oscar winners or the wins for 2016 as well so there you go there's using the Union method to join together lots and lots of disparate ranges of cells I'm just going to remind elites of all charge routine again at this point you to get rid of a few things then think about a couple of other things we could attempt to do okay let's go for one more complicated one let's say we wanted to build a chart made out of only those films that have won at least one Oscar so we wanted to eliminate the row is where the wins column contain a zero so to make that work let's head back to the VB editor and I'm going to maximize the VB edits at this time and create a completely new sibling is a slightly more complex routine this one so that's create a sub called create oscar-winner chart and I'm going to start by declaring a few variables to help us loop over a range of cells and then build up the range of cells that's going to form the chart data source so let's say something like dim chart cells first of all as a range and we'll also have dim film as range and we'll also have dim films as range and not all these are absolutely necessary but it will help us to work out exactly what we're doing when we build up this sequence of code next we'll make sure that we've selected one of our awards worksheets I'm just going to give myself a few pipe lines just to scroll this up so you can read a bit more clearly what I'm doing so I'm gonna say WS Awards 15 select this time we can easily change that a bit later on now what I'd like to do is set up which range of cells the films range contains so at this point all I'm going to do is set the film's range equal to cell a4 down to whichever cell is at the end of the list from cell a3 so I'll just give me the range of cells in column a containing films so to do that I'm going to say set films equals range a4 which is the first cell containing a film name comma range a3 which is the top of the list dot end Excel down which will take me right down to the very end of it once I've done that I could begin looping over the range of cells by saying for each film in films so just a standard for each liver the type we've used many many many times in previous videos close the 43 by saying next film and then what we're going to do is work out how to build up the range of cells and store it in the chart cells range I'd like the chart cells range to start by containing just the row of column headers so I'm going to say just below where I've said set films equals and as a set chart cells like run chart cells equals gather eventually and I'm going to say range a3 comma range a three dots end Excel to write I could have easily just said here range a3 to see three because I know that there are only three columns of data in the entire worksheet but just in case I decide to modify my worksheet later on and include some extra columns I might want to make sure that that extends all the way across all the columns in that block of cells so having set that up that's it before I begin looping inside the loop what I'm going to do now is check which what value the cell in column C holds and as long as that is greater than zero I want to add that entire row into the chart cells range so to make that work we need an if statement so I'm gonna say if film dots offset 0 comma 2 dot value is greater than 0 then give myself a couple of blank lines and then say end if so I'll write a block if again many videos in this series of using gift statements and we've done this idea of looping over collections of cells in many videos as well so not going into too much detail about how this works there's plenty of reference material in this in this video series so having got that if statement set up what I want to do now is if that is the case if I'm looking at an oscar-winning film which will fortunately xq exclude things like the Fifty Shades of Grey and other ones that aren't particularly good but less said about those the better if that's the case I'm going to say set chart cells equals Union so I'm going to use the Union method again to essentially extend what's already in the chart cells range so I'm going to make chart cells equal to itself or a union of itself and a new range that's going to start with the film cell so range film comma film end XL to right so again just in case I decide to extend the number of columns in my list later on excuse me just missed one extra close parenthesis if that's the case then that will add each row of data for oscar-winning films to the chart cells range now we only need two more lines of code to actually create this chart the first thing to do is select the chart cells that we've built up in the variable so we can say chart cells dot select you can apply any method to the chart cells variable is just a range so it's like saying range a1 that's a lot you can do anything to that range that you could to a normal range of cells then all we need to do is create the chart and we know how simple that is we could say charts that add to create a new chart okay so I just restored down the window at that point and have a quick look to make sure what we'd expect to get from Awards 2015 so we're going to eliminate all the non oscar-winning films sadly means we're gonna lose guardians of the galaxy but just to get the coworking interpreted it does work if I were to run that subroutine and go back to the chart that gets created we'll see that only the Oscar winners from that list if I switch back to us to the awards 2015 sheet we should be able to see it has indeed not selected the non Oscar winners just to check that that works for any worksheet we might as well we can change this Awards 15 to award 16 and then we could amend the subroutine again and we'll end up with a new chart of films the won Oscars in 2016 so again the non Oscar winners have been removed just checking on Awards 2016 you can see that we've sadly lost things like ex machina and the Martian which is very sad in Star Wars of course as well but small price to pay to get rid of Fifty Shades of Grey dreadful dreadful movie or so I've heard I've not actually watched it I don't know I wouldn't waste my time but apparently it got nominated for an Oscar for the music it would certainly wasn't anything to do with the acting or the directing so at least there least has to be said for it anyway and now you've got the opportunity to modify your if statements you can check for any conditions here so you could you could build a list of cells to be selected to generate your charts based on any criteria you can think of it could be films have been nominated for more than one Oscar it could be films that have got the letter X in their name you could do anything so as long as you know something about writing LIF statements and writing different criteria you can build your chart of whatever range yourselves you like okay just a couple of other quick things to mention about the charts dot add method I'm going to scroll back up to the top of this module and then run the delete all charts routine again and then let's have a quick think I'm going to make in fact I'm going to make a completely new module for this next artists action so I'm going to right click on insert a new module and then I'm going to create a new subroutine called creating charts and choosing positions little slightly longer than though I intended to do but what we're going to do then is say WSHH WSHH Awards 15 I think not select and then I'm going to say range a3 to see 19 dots a let's just do a really really quick simple one then I'm going to add a chart but this time I'd like the chart to appear in a set position so so far all we've done with the charts add method is accepted that it will appear to the left of whichever sheet is currently active on screen and Lanica runs do this time is control exactly where the char goes this is very similar to how we add in new worksheets and choose their positions if I say charts dots pad and then type in a space you'll see there's a list of four optional parameters for the the add method of charts before after counting type the only ones we're concerned with here are before and after so before allows you to refer to any sheet and the sheet that you refer to the chart will be inserted to the left of that sheet so without having to select the sheet I could say for instance worksheets manual now my case I'm just going to say W s menu so if I were to run this subroutine right now I ought to end up with a new chart sitting to the left of the menu sheet so I can run that and that is indeed exactly where the chart goes now although that works to position the charts where we wanted it the sad side effect of that is the chart doesn't contain any data so when you choose to position the chart exactly where you want it to go it loses the ability to base the chart or set its source data based on the range of cells that you'd selected a bit of an irritation so we've got a couple of ways around that what we could do is after we've added the chart we could set a reference to it and then use one of its methods to set the range of data that should populate it alternatively we could just add the chart wherever it should go and then move it afterwards so it doesn't really matter which way around we choose to do this but it's nice to be able to show you how to set the source date of a chart it's something that we need to show you in this video anyway so let's assume that we wanted to add the chart first and position it exactly we wanted it to go what we can then do is use the active chart property so if you say active chart when you created you chart it becomes the active one automatically so if you say active chart dot there's a method here called set source data so I say set source data followed by a space you'll see as a single compulsory parameter source as range so what we need to do at this point is refer to the range of cells on the correct worksheet that we want to use to populate that chart so I can say WS Awards 15 big run SS Awards 15 range a3 to see 19 that means that I don't have to select the cheat or the range of cells first so let me just comment those out temporarily and then if I run this subroutine one more time we'll see that we get a new chart inserted to the directly to the left off menu and then it's populated with the range of cells from the awards 15 sheet so that's one way to get around the problem this does make it slightly more awkward elevator refer to the more complex ranges of cells that we were looking at earlier on so we wanted to set the source data and get a more complex arrangement it might make more sense to go back to the award sheet which means then you'd have to remember which work which chart you just created to go back to that and set its source data so and so it can cause a few more complications we'll talk about ways to solve that shortly but it might make more sense rather than choosing where the chart goes I'm going to comment out the active chart set source data line and bring back in the the awards and select for the cells rather than choosing where the worksheet or sort of where the chart goes when you create it or when you add it what you can do instead is say afterwards you could say active charts dots move and then the move method has got a couple of parameters as well it's actually identical to the parameters for the add method you can choose their sheets before or after the sheet that your chart will be moved to so again if I just say before WS menu my new chart will be added and then move to the left of the menu sheet so I run that one this time I will end up with yet another sheet to the left of the menu sheet but again this time is populated with the correct range of cells based on the ones that I'd selected just as a final couple of neat things you can do with this if you wanted to move your charts that it was always either the first or the last sheet so you didn't want it to be based on the position of any other specific sheet in the workbook then you could just refer to the sheets collection so here rather than saying move before WS menu I could say move before sheets one so the the sheets collection refers to any worksheet or chart sheet in the web book and they have an index based from one so sheets one will always refer to the very first sheet at the left hand side of the sheet tabs whichever that happens to be so if I do this one this time I run this subroutine a few times I will find that each new chart gets inserted as the very first sheet if I wanted to flip that around and insert my new chart and move it then to the very end of the sheets I could say add to chart move and then I can use the after parameter so rather than using the before parameter which is the first one I could either just type in a comma to move on to the after parameter or I could actually explicitly say after colon equals to name the parameter I don't want to insert it after sheets one of course I want to insert it after whichever sheet is the or has the highest index number so the easiest way to achieve that is to say insert after sheets not one but after sheets sheets dot count so the count property returns the number of sheets in the collection so this time if I run the subroutine again I'll find that my new chart and gets inserted to the right-hand side and each time I do that it will expand and go to the right of the last sheet so that's a few quick simple thing you need to control the position I'm just going to go back to module number one at this point and tidy up a little bit by deleting all the charts and then let's move on and see a couple of other things we can do the next few things we're going to do in the video are going to rely on it being able to refer to the chart that we've just created now one simple way to do that is if you know that the chart becomes the active chart when it gets created you can simply use active chart but of course then if you move away from that sheet and select a worksheet for instance you lose the ability to refer to it as the active chart so I'm just going to taught you really quickly about a couple of other ways to reference a chart so just a quick stub referencing charts and then let's say we in fact them just quickly copy and paste that bit of code there from the previous routine so go to the wall sheets like Ranger 33 to see 19 and then select and then you create the chart so of course you can say as we've just seen use a active chart that refers to the child has just been created type in a full stop you get the list of or the intellisense of methods and properties for the active chart if you knew what the name of the chart was you could also refer to it by names you say charts open parenthesis and double quotes and say let's imagine it was called chart one of course you wouldn't necessarily know that was called chart one but the same thing if you say charts chart one you can apply any method and property to it in the same way so if you've got a chart that's sort of that belongs to the workbook and is unlikely to be deleted give it a sensible name and you can reference it like so you can also give charts code-named as well so in fact let me just comment out that line briefly I'm just going to run this subroutine at this point and I get a new chart who's called chart 30 and it's got a code name of chart 30 as well but just like with any standard sheet in Excel you can rename both this tab name it's sheet name and its code name so they go up to the top of the properties window and find the name property I'm going to call this one CH oscar's which one was that Oscar's 15 wasn't it so let's go for CH Oscar's 15 and then I could change its tab name as well so I can change that in the properties window as well so I can say let's call that Awards chart 15 so that means I can reference that chart in a couple of ways I could say charts Awards chart 15 and then do things to it like so but more conveniently I could also say CH Oscar's 15 which is its code name and then apply methods to it so for this video we're going to be creating charts a lot so this this technique it's great as long as you've got a chart that you know is pretty much always going to be there give it a sensible fixed name and a fixed code name and you can reference those in your code but in this video we're going to have be having charts that get created and then deleted fairly quickly so the standard way we're going to reference charts that we create from this point on is using chart variables so the top of this subroutine what I'm going to do is Claire a new variable called CH as a chart and then we're going to use the add method to return the reference to the child we've created if I just type in a space after the add method you'll see at the end of the parameter list it says it returns an object so I can return the object that gets returned by the add method and store it in the CH variable so I can say set CH equals charts dot add and what that means is that subsequently in the rest of this procedure now I can say CH dots to reference that charts and then do things with it so there you go there's a quick little look at various things you can do for referencing charts so I'm going to say let's say set source data and then I can make that equal to a particular range of cells now that you know various ways to reference a chart whether it's a brand new one that you created or one that already existed we're going to look at a few different things you can use and modify them or edit them so I'm going to just go back to module number one briefly and run my delete all charts routine to get rid of everything and then back into module two I'm just going to reuse my referencing chart subroutines let's just change its name so it's called editing charts instead I'm just going to get rid of this little set source data method so the main idea in order to edit a chart of course you need to be able to reference it first and we've just talked about various different ways you can do that once you've got a reference to a chart there's a whole range of methods and properties you can use to edit it in various different ways just before we look in detail at some of these specific methods and properties it is worthwhile quickly mentioning something called the chart wizard now the chart wizard method if you're very very old like I am you may remember from very old versions of Excel when you created a chart there was a little dialog box a step-by-step wizard that you followed to choose the chart type the range of data you're going to use the titles and the axis labels etc so you did all that in a simple dialogue box to set up like the main options on your chart the chart wizard method allows you to do exactly the same thing so for instance I wanted to set the source data you can see that the chart wizard had a source parameter they're all optional the parameters here are optional and you can change all of these things using dedicated properties and methods as well you don't have to use a chart wizard to do it but if I said let's say rather than using the set source data method for the chart like I say chart wizard and then say source : equals and then let's just say something like WS Awards 16 range a three to see mmm C 10 let's go for so if I were to run that circle to see now I'll end up with a new chart that originally did indeed have this range of cells so it's Awards 15 a 3-2 C 19 but it's now changed by changing the source of the chart wizard to this new range of cells on the wards 16 so just to prove that that's the first few films from the 2016 sheet okay so you can use the chart wizard to make fairly simple changes I'm going to ignore the chart wizard from this point on what we're going to do instead is look at the dedicated properties and methods of editing charts so let me just quickly delete that line and let's look at a few other useful things we can do okay so I guess the biggest thing you can change about a chart is the chart type that you insert by default what we've been getting is a standard clustered column chart which happens to be the default chart that you get in when you first install Excel but if you wanted to modify that it's incredibly simple CH dot charts type and then you can make that property equal to any of the constants in this enumeration so there's every single chart type you can think of in this list so let's go for a fancy maybe a 3d 3d clustered column or 3d column clustered as it's called there so having done that if I were to run this routine again and we'll end up with a slightly fancier completely useless I find it's very very difficult to read exactly which value the bars point to but just to prove that you can change the chart type with a really really simple property depending on what range of data you've selected let's just modify this I'm going to go for a three to be nineteen and I'm going to go for let's say at CHR type equals Excel 3d pie exploded which sounds like the most exciting chart possible if I were to run that one this time again you've got a completely different chart which again familiar it's completely unreadable that me that's virtually meaningless based on the idea that based on the data that we've got here but nonetheless you can quickly and easily change the chart type just by modifying the chart type property let's just change things again a little bit so that we're getting a different range of data I'm going to go back to selecting just range a3 which will mean that I'll get the cut the entire table of data at the current region for the chart and that means I don't want to use a pie chart I've got two separate series of data the Oscar nominations and Oscar wins so when we go back to I'm going to choose explicitly the excel column clustered which is the default chart type anyway I didn't need to do that but what then I'd like to do is change a few other things like maybe create a chart title now some things are fairly easy to change on charts like for instance adding a legend to the chart if you say CH dot has legend then you can simply make that property equal to true or false so when you've got more than one series of data the legend will give you the color coding the little key which indicates which which series each color represents so if I were to run that one this time I'll end up with my clustered column chart with a little legend on the right hand side if wanted to turn that off I could just set house legend equals false and I would remove it when the chart gets created adding the title is similar actually if I say CH dot has title I can make that equal to true but that doesn't automatically assign text to the title necessarily if wanted to modify the text of the title what I then have to do is say CH dot charts title dot text equals and then assign the exact string of text that I want to appear in the title so in this case I'm going to go from number tsa's wins I can't before the typing out nominations so currently the chart that I just created it doesn't have the title if I go back to the code room run this one again find this time the chart does have a title now if you're using Excel 2013 or later then there is a slightly easier way to apply those same properties we've just changed a legend in the title if I just comment these lines out temporarily and then just run that stability in one more time to show you what we get we do actually get the legend turned on automatically but the title doesn't appear now if I go back to the VBA editor and I change you might have noticed a few times that I've been saying charts dot add there's another intriguing looking method called add to inventively titled good going there Microsoft so add to now the other two method just adds another chart to the to the collection of charts but it's got a slightly different parameter list and only you can see here it got before after encount and instead of type it's got a parameter called new layout and like me either true or false and if you said it's true what the documentation says is that the chart will use the new dynamic layout to add the chart elements to be brutally honest I can't tell the the slightest bit of difference between using new layout true new layout false but one if that that add to does have regardless of the new layout property is if I run this one this time you will find that you get a chart title automatically created and it also includes the legend but in a slightly different position so the legend will only automatically appear if there is more than one series in the chart so just to limit it what I mean by that if I go back and change my range to a3 to be 19 in this example and then run this one again without - then I'll find that because there's only one data series it automatically excludes the legend there's no point in having it if there's only one series and it sets the title automatically if I go back to the VB editor again I just change that back to charts dot add and run that one again I will see this time that I get the legend added even though it's completely unnecessary but I do also get the title here as well so a couple of slight variations with there with the add and add to methods because add two is only I believe introduced in Excel 2013 I'm going to continue with the add method just in case you're still using Excel 2010 or 2007 so you can still follow along with the video so chart out and I'm going to switch back all these properties again so that everything gets set up the way that I want it okay let's just tidy things up again a little bit by going back to module one and running delete all charts just to tidy things up and then let's go back to module to look at a couple of other things we can modify now we've already looked at adding an overall chart title and changing its text what if you want to do the same thing for the individual axes on our chart so in the column clustered chart that we're creating we've got an x-axis the horizontal axis and y axis the vertical axis if you want to assign titles to each of those then you have to reference each axis individually so we say CH dot you'll find that there's an axis collection so the axis method allows you to return a reference to a single axis so you do this using a constant so for the category axis or the x axis you say excel category and then you can say dot has title suddenly the intellisense breaks down at this point unfortunately but I can say house title equals true and then I can say CH axes I should probably use a little with block here but I can say CH axes again open parentheses Excel category and then I can say axis title sorry big one dots axis title I'll get there eventually so awkward when you know can you see intellisense to help you shows you how often my typing actually is maxi's title dot txt so it's essentially the same as we've done for the chart title and you can make that equal to whatever you like so I'm going to change that so it's called to me write film name and then we can exactly the same sort of thing for the value axis rather than write all that out again and waste all that time I'm just going to copy and paste those two lines then I'm going to change the axes that I'm using this time from excel category to excel value and then again change excel category to excel value and I'll just change the text of that so that it's called something simple like let's just say quantity all right so having done that let's just modify my range of data again I'm going to go back force just cell a3 so I get the full table of data this time run that so it's in one more time and I'll end up with a new chart that's got a film name x-axis category axis title and a y-axis or a value axis title there as well another potentially useful element we could add to the chart would be data labels at the top of each column so that'll help the user to read what the value of the column Angeli is so I switched back into the VBA editor if I wanted to add in a set of data labels for a single series then the simplest thing way to do that is to refer to the series collection method of the chart object as a CH series collection the series collections just indexed starting a 1 so wanted to refer to the blue columns that's series collection number one and then I can say sadly the intellisense breaks down but in a similar way to has title I can say has data labels equals true and then in where I to run that subroutine at this point I'll get a new chart with data labels as you can see just at the top of the blue columns if I wanted that to be assigned to the second series as well I could just copy and paste and then enter the number two for the second series run that one again and I'll get data labels at the top of both columns that's all well and good as long as I've only got a small number of series if I've got lots of series that gets incredibly tedious so instead what we could do is loop over the series collection so I could do that by declaring a new variable so I could say something like dim s as series and then later on if I just comment out my two lines that I've just added in down here I could write another for each loop so I could say for each s in ch series collection I can say next s to close loop and then inside there I can simply say S dot and the nice thing about having declared a variable to hold the series is that we now get the series of the intellisense for the series object so I could just say here has data labels equals true there's also a slightly different way to do it you might have just spotted there in the intellisense there's an apply data labels method this is quite nice because it allows you a set of parameters to specify exactly what it is you want your data labels to show so by default they will show you the value so that's their excel data labels show value that's the default value that it will display you could also make it show a label or show non or show a percentage some of these are only relevant to things like pie charts and donut charts so for instance the show percent and show label and percent would only work for pie charts and donut charts I'm gonna own excuse me I was going to leave it saying show value so I'm just gonna leave that as a default and then this time I run the subroutine one more time I'll assign data labels to all of the series but this is great because this would work whether I had two series or 20 it would apply data labels to every single one now we've only done a few very basic things to change the appearance of our chart but already the code is getting quite long and tedious to write I have to admit so there's a quick simple solution if you wanted to just assign a few default settings for things that would appear on the chart there's a set of options available in a chart called quick layouts so if I click on to the charts that I've created and go to the design tab in the ribbon the second button along says quick layout if I click on that drop-down list there it's just a set of preset options to do with things like access titles and chart titles and data labels and legends and gridlines and some basic background changes as well so you can see that hovering over each of these gives you a different option now there you can apply these quick layouts fairly easily in code so just to show you how that would work I'm going to go back to the VB editor I'm going to create a brand new separate subroutine that will demonstrate this so let's call this sub change chart layout something along those lines at this point I'm just going to scroll back up to the previous subroutine Armel cheat a little bit or copy and paste some code so let's have a quick look at copying and pasting everything out to the part where we create a new chart scrolling back down again we can paste that in and then we can get rid of this series variable we don't need that here at all so we'll just have added in a brand new blank chart at this point so not blank chopping on it will be populated we haven't designed anything like titles and legends and data labels etc so we need to do in order to make that work is say CH dot apply layout so there are two parameters they apply layout method the first compulsory one is the number of the layout now according to microsoft's documentation and it still says the time of recording microphone tation says that you know assign a number of one to ten the little lie there you naturally at least in Excel 2013 saying numbers one through eleven there are actually eleven different layouts so the documentation still haven't changed yet I don't know that's an oversight or not so let's just choose a number team let's use num between one and eleven I'm going up at eight now you might have noticed as well that there's a separate chart type parameter parameter here for the apply layout method so if you didn't haven't already assigned a chart type you could assign it here as well I'm going to stick with our bog-standard clustered column chart so having done that if I were just to run that subroutine at this point have a quick look at what I've got it made a few modifications without me having to write lots and lots of code to to change that I'd still have to then refer to the axis titles to actually change the text and the chart title to change the text but at least it saved me a tiny little bit of effort now at this point if you wanted to go through all of those different layouts to work out which was the best one for you to use it's going to be quite tedious to manually edit that number of them run the subroutine each time so instead what we could do is loop through the numbers 1 through 11 and then create a new chart and applying apply that layout to it so before I do that I'm going to go back to module number 1 and run the delete all charts routine again to get rid of all the existing charts then back into module 2 and we'll make a couple of modifications to our change chart layout routine we'll start by declaring a new variable I'm going to call mine I as integer and then instead of just adding one single chart what I'm going to do is run a loop that says for I equals 1 to 11 then I'm going to create a new chart and then I'm going to apply a layout layout to that chart whose number is equal to I then I can say next I and that will run through to generate 11 new charts it might be worthwhile just so we can see which chart layout we've applied changing the name of the chart or changing the charts title as well so we could say for instance CH dot has title and then we say CH dot Augsburg button has title equals true sorry big one I forgot to assign a value to them to the property true and then we could say CH dot title dot text equals and then I'm going to say chart layout ampersand I so that will give us the the number of the chart layout so having done all that if I just run the subroutine in that point I ought to end up with oops poke button I completely chose the wrong method there sure the wrong property is chart title isn't of course time to apologize and so see a chart title dot txt okay lo just from now on one more time and then we should see at least 11 new charts should appear and each one should have a title which corresponds to the layout that we've chosen and each one should be ever so slightly different in terms of its layout so there we go that gives you a slightly easier way to pick the one you want to or that one's got a data table look so there you go that's how you can print out all the various different chart layout so you got a slightly easier to choose I'm going to go back now to the VB editor and back to module number one and I'm going to run the delete all chart routine again just to tidy things up before we go back to module number two and have a look at something else we could try now the other major thing that you can do to affect a child's appearance is to format the individual elements of the chart so although we've made different things appear on the charts and change their positions and so on using layouts we haven't actually formatted any of those items and this is one of those are the topics for char's that I could probably just fill up an entire video of all the various things you could format on a chart because there's a bewildering number of those things let's just quickly create a new subroutine to create a basic chart so I'm going say sub change chart colors let's say and then I'm going to copy and paste a few lines of code from the previous sub regime the first four lines are in fact and then let's just make sure that we've actually created a chart as well so I can say set CH equals charts dots and and then if I just run this subroutine at this point to create the chart just so we've got something to work with you can see that for every single item on a chart that I can select there's a format tab and I can format each individual thing in a huge variety of different ways so that goes to the background the gridlines the axes and number formatting the font formatting having got a title on here but I could format the title and the legend you get the idea it gets horrendously tedious so just in the same way that there's a set of quick layouts to allow you to turn on lots of various up some options on the charts in a quick simple way there are some quick easy ways to get formatting of charts done as well so let's go back to the design tab there are two main things we can do here there's a change colors option which gives you a range of different color schemes you can apply to the chart so some of these are some more hideous than others and there are also a set of chart styles if you look in the chart Styles list these apply a range of both formatting options and layout options at the same time so you see that they change the gridlines in some cases and change the legend in some cases as well so that's a sort of a combination type thing let's have a quick look at the change colors option though first if I just go back to my VB editor and I'm going to just tidy up my chart okay I'm just going to manually this one actually in fact and then back in the VB editor what I'm going to do is loop through that range of numbers again just as we did for the layouts this time the color schemes the numbers range from 1 to 26 or at least they do on my machine that might be different depending on which version of Excel you're working in but all I'm going to do is save for I equals 1 to 26 then I'm going to create a new chart and I'm going to set its CH dot chart color spelt the America mode without the U which I often get wrong sorry about that so see a chart color equals I then I can simply say next I and that will loop through the range of colors just in the same way that we looped through the range of chart layouts once again is probably worthwhile just quickly having a title that demonstrates or at least labels what we've actually done so let's create copy and paste those two lines from the previous routine and I'm going to call this chart color and don't worry I'll spell it there well the way that Excel would like me to spell is let's put it that way let's not talk about right and wrong ok so having done that let's run that subroutine and have a look we should end up with 26 new charts marvelous and then each one of these will have a quick look should have a different color scheme so say some somewhat more hideous than others but again you can scroll through these and you've got a little label that stopping the chart cycle that tells you which color scheme it is so it makes it easier for you to pick one from the list okay let me just go back to the VB editor again and I'm going to go back to module number one and I'm going to delete all those charts just to tidy up a bit and then the one of the last things were going to look at in this video we're gonna have a look at these chart layouts sorry back from the chart Styles not chart layouts now chart styles in them but just in the same way that colors and layouts are so we can use exactly the same sort of technique we've just used to loop through the colors and layouts so I'm just going to copy and paste my entire change chart colors routine and then paste it in a couple of lines further down and then of course we'll need to rename the subroutines let's call it change chart styles okay so I'll use the same kind of basic arrangement using the same data but again the thing that we don't want to do here is change the chart color so let's change that so it says CH dot chart style and again you can make this equal to the value of the integer variable the counter variable will add in a title and change the chapel at the chart title text so it's chart style rather than chart color this time and then all we need to do is work out what range of chart styles were allowed to use now the documentation again just like it lied about the layouts there were 11 rather than just 10 it lies about the chart style numbers as well the documentation says there are 1 to 48 different chart styles and it is quite true actually if I this 249 and I try to run this one it's going to crash when it reaches 49 just just to demonstrate that I'm going to run this one I'll end up with 49 different charts but it will crash in the 49th so the specified dimension is not valid if I click debug you can hopefully see that if I hover over the letter I or it will show me that it's 49 so that's the point that it's failed ok so I'm just going to stop that and then go back into module number 1 and of course delete all those charts I don't want to create that many again and then go back into module number 2 I'm going to change the app the number this time to flight bewilderingly 352 just through experimentation I discover this the documentation doesn't say this I'm gonna go to 352 now there are indeed 48 original chart styles but once it reaches 48 there's a big gap and the numbering picks up at 201 bizarrely so what I'm going to do is say in here I'm going to say if I equals 49 then I'm still going to make I equal to 201 so it will change the value of my loop counter so it will start at 201 at that point then continue increasing until it reaches 352 and that's the that's the biggest number that I could find in that list so at this point I'm just going to hit the play button and just cross my fingers that it doesn't all crash but I ought to end up with quite a significantly larger number of charts there eventually and hopefully I might get a not responding message just briefly but eventually I'll get there wow look at all those that's pretty impressive now it's entirely up to you how much time you want to spend picking through all of these just so you can see which particular chart style you might want to use it's probably easier to stick with a fairly limited it's not a huge amount of difference between these some of these look incredibly incredibly similar I'm certainly not intended to go through and show you every single one that I've just generated but there you go that's how you can list out the variation chart styles and hope that you can find one in that list that might look at least a little bit interesting to you and one that you might actually want to use in the real world let me just go back into module number one and just tidy all this up one more times back into module one I'm going to delete all those charts I just get rid of them all and tidy things up a bit then back to module number two okay so for one last option in this video as a way to format charts quickly I'm going to look at something called a chart template now chart templates are customized things you create yourself and they combine a combination of formatting options and layout options and style options and even chart types as well but the problem is you have to create them manually first of all so what's going to do is create a quick extra subroutine which is going to create a new chart that we're going to use to create our chart template it's going to say create new chart with template and then I'm simply going to say dmca chars chart and then let's just do a little bit of copy-paste so we can create a chart with some data in it I'll call you paste those two lines which selects the worksheet and sell from the previous subroutine and then I can say set CH equals charts dot add okay so just having done nuts I'm going to run that's everything and I'll get my bog-standard basic chart and then it's a bit of a case of manually formatting this to get it to look the way you want so in Excel itself you can do things like so on various options so if I go to this little plus symbol at top right hand corner I can choose to put on the chart title for instance and then I could maybe format that I could put on some data labels as well let's put on some data labels I could do a bit of formatting so I'm going to change the format of the chart title so it's got some horrific word art type style I could change the background color of the chart let's give that some kind of let's not make it too horrific let's make it sort of pale greyish and then let's change the font of there the axis labels sorry the category labels at the bottom you get the idea of the I think it's all about just changing the chart to look the way you might want it to look so we could go a little bit further than that let's just maybe change the chart type as well so let me just give this a default chart type of let's go for let's go for one of these 3d columns shall we say there we go a 3d classic on completely unreadable there we go super right so having done all that what I'd like to do now is save this however hideous it looks and I do apologize for how ideas this looks I've never been them I've never had much taste I suppose when it comes to colors and so on that's why I'm more of a programmer than a designer so let's right right click somewhere on the background of the chart I'm going to choose to save this as a template so I click save as template it's going to take me away to a specific folder that is part of your user profile so what I could do just for the moment going to need to refer to this path later on so it's going to click into the address bar there and just copy that to the clipboard for now and I'll give this chart template a sensible name I'll call it hideous why not I can spell that properly hindi yes there we go marvellous and then just going to save that so having saved it that chart template is now available for me to apply to any new chart that I create I didn't have to save it in that particular folder by the way you can save your chart templates anywhere else but it makes more sense to save it in there in the default location so let me just right click and remove that chart that I've just mangled and then go back to the VB editor and all I'm going to do now is with the new chart that I create and ACH dots apply charts template I'll need to pass in the file name and folder path as a string so if I type in a set of double quotes there I'm just going to paste in the path that I've just copied and then I can tag on to the end of that and backslash and then the name of the file that I saved was hideous dot CRT X and all right so here's the idea if I use the f8 key just to step through now just so you can see that this definitely does create first of all the normal box on the chart whenever I want to apply all the formatting options I've just saved I can apply my chart template and that will make it look as hideous as it was before but I can now do that for any new chart that I create so there you go there's a whole range of different ways you can change the appearance of your charts okay well that's basically it for this first introductory video I'm working with charts in Excel VBA of course we have uncovered absolutely everything there is to know yet about charts that's going to take several more hours to accomplish but this should just give you a nice introduction so now you know how to create charts from scratch you know how to assign a certain data range to them and you know a few basic things about formatting them and hopefully making them look a little bit prettier than the ones I ended up with at the end of this video several more videos come in this little sort of mini series if you like working with charts in a bit more detail but thanks for watching this one see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 40,576

Rating: 4.9808154 out of 5

Keywords: excel vba, visual basic for applications, vba, introduction, charts, graphs, data source, setdatasource, chart type, chart layout, chart style, chart template, wise owl

Id: fWyw4DJCSsE

Length: 61min 42sec (3702 seconds)

Published: Wed Nov 16 2016