In Episode #1291, Bill looks at manipulating Charts with Excel VBA. Its easy to control the exact location of your Chart by designating its placement on your worksheet. ...a podcast from Ch.11 -"VBA And Macros: Microsoft Excel 2010", from Bill Jelen.
Transcript of the video:
MrExcel podcst is sponsored by Easy-XL.
Excel VBA, Chapter 11, Chart Location.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Oh! Well, we're working through the VBA and Macros book here chapter 11 is all about charting.
All kinds of charting Macros, but the one that I chose for the the podcast for this chapter is about controlling the location of a chart.
So, I'm going to do this, I'm going to turn on the Developer, tab, go to the Developer tab, turn on Record Macro, just create a quick little Macro here that creates a chart.
I'm just gonna press Alt+F1, create a default chart and everything and we'll go back to Developer, and click Stop Recording.
All right! Now, I'm going to actually delete this chart.
We're going to take a look at that Macro that we just recorded, Edit, and you'll see here that we're using a method called Add Chart.
Add Chart method and then the Macro Recorder selects that chart which then allows the next two lines to work on the active chart.
All right! Now, another way to go here yeah we've go to same code that, I run in module 2, is where we kind of do this all in one step.
So, the add chart method we specify the type it's a clustered column chart, we specify the top-left width and height and you'll see this a lot in Excel help or an Excel books.
They'll tell you to use this top left with a height, but the problem with that what is that 100 mean, what do they, what are they saying there that's the number of pixels from the left edge of the Excel screen.
Okay so, let's say that we're trying to put a chart in column E and we calculate that E is 100 pixels from the left end.
Well, the danger there is that someone might take our spreadsheet and adjust some column widths.
So, if this is currently 100 pixels, and then they do this.
Well now, it's not 100 pixels anymore and sure it's going to appear in the wrong place, it just seems like a really, really dangerous way to go.
So, I'm going to show you some other code here.
I'm going to go to module 3, where I've rewritten that code we just used to say, hey for the left, I'm not going to say that it's 100 pixels from the left edge.
What I want you to do Mr. Macro, is go out and look at range E5, right now, go see we're E5 is and tell me where the left edge is and that's going to say, Oh! That's 116 pixels from the end or 300 pixels from the end.
Wherever, E happens to be and so, that property tell me where the left edge of E5 is, becomes the setting for the left edge of the chart, for the top of the chart, we look for the top of cell E5, for the width and height.
The width we're going to use E5 to I5 in other words look at that range E to I, figure out how wide it is, and then for the height use E5 to E13.
So, this is going from Row 5 to 13 and from columns E to I.
Let's run the chart or run the Macro, bam I'll go back and look at Excel as I scroll down here you will see that the chart is now perfectly located from E5 to I13.
All right and the beautiful thing about this as I said is if someone screws with a spreadsheet, they may column C wider, I don't know why and they make Row 9 taller again I don't know why, the macro looks kind of in real time to see where E5 is and where I13 is and it makes sure that the chart fills that space and appears in a correct space every time.
So, I think that's a pretty cool trick there rather than, just hard coating the top left width and height.
We asked Excel you know hey go figure out where E5 left is and that solves a problem very, very well.
Oh! Hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel VBA, Chapter 11, Chart Location.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Oh! Well, we're working through the VBA and Macros book here chapter 11 is all about charting.
All kinds of charting Macros, but the one that I chose for the the podcast for this chapter is about controlling the location of a chart.
So, I'm going to do this, I'm going to turn on the Developer, tab, go to the Developer tab, turn on Record Macro, just create a quick little Macro here that creates a chart.
I'm just gonna press Alt+F1, create a default chart and everything and we'll go back to Developer, and click Stop Recording.
All right! Now, I'm going to actually delete this chart.
We're going to take a look at that Macro that we just recorded, Edit, and you'll see here that we're using a method called Add Chart.
Add Chart method and then the Macro Recorder selects that chart which then allows the next two lines to work on the active chart.
All right! Now, another way to go here yeah we've go to same code that, I run in module 2, is where we kind of do this all in one step.
So, the add chart method we specify the type it's a clustered column chart, we specify the top-left width and height and you'll see this a lot in Excel help or an Excel books.
They'll tell you to use this top left with a height, but the problem with that what is that 100 mean, what do they, what are they saying there that's the number of pixels from the left edge of the Excel screen.
Okay so, let's say that we're trying to put a chart in column E and we calculate that E is 100 pixels from the left end.
Well, the danger there is that someone might take our spreadsheet and adjust some column widths.
So, if this is currently 100 pixels, and then they do this.
Well now, it's not 100 pixels anymore and sure it's going to appear in the wrong place, it just seems like a really, really dangerous way to go.
So, I'm going to show you some other code here.
I'm going to go to module 3, where I've rewritten that code we just used to say, hey for the left, I'm not going to say that it's 100 pixels from the left edge.
What I want you to do Mr. Macro, is go out and look at range E5, right now, go see we're E5 is and tell me where the left edge is and that's going to say, Oh! That's 116 pixels from the end or 300 pixels from the end.
Wherever, E happens to be and so, that property tell me where the left edge of E5 is, becomes the setting for the left edge of the chart, for the top of the chart, we look for the top of cell E5, for the width and height.
The width we're going to use E5 to I5 in other words look at that range E to I, figure out how wide it is, and then for the height use E5 to E13.
So, this is going from Row 5 to 13 and from columns E to I.
Let's run the chart or run the Macro, bam I'll go back and look at Excel as I scroll down here you will see that the chart is now perfectly located from E5 to I13.
All right and the beautiful thing about this as I said is if someone screws with a spreadsheet, they may column C wider, I don't know why and they make Row 9 taller again I don't know why, the macro looks kind of in real time to see where E5 is and where I13 is and it makes sure that the chart fills that space and appears in a correct space every time.
So, I think that's a pretty cool trick there rather than, just hard coating the top left width and height.
We asked Excel you know hey go figure out where E5 left is and that solves a problem very, very well.
Oh! Hey I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
