Chapter 11 takes a look at many charting tricks. This podcast shows you a cool way to make sure that your charts appear in an exact cell range.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Excel VBA Chapter 11 - Chart Location Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, well we're working through the VBA 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, it creates the chart.
I'm just going to 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 ] 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, the 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're going to some code that I run in module two, is, what we going to do is, 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, there's a lot in Excel help or in Excel books, they'll tell you to use this Top, Left, Width and Height but the problem with that, what is that 100 mean, what are 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 a 100 pixels from the Left end.
Well, the danger there is that someone might take our spreadsheet and adjust some columns.
So, if this is currently 100 pixels and then they do this.
Well, now it's not a 100 pixels anymore and charts 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 three where I've rewritten that code that 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 where 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, or wherever it 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, I said is, if someone screws with the spreadsheet, they make 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 it is and where I13 is and it makes sure, that the chart fills that space and appears in the correct space every time.
So, I think that's a pretty cool trick there, rather than just hard coding 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.
Now, well we're working through the VBA 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, it creates the chart.
I'm just going to 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 ] 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, the 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're going to some code that I run in module two, is, what we going to do is, 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, there's a lot in Excel help or in Excel books, they'll tell you to use this Top, Left, Width and Height but the problem with that, what is that 100 mean, what are 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 a 100 pixels from the Left end.
Well, the danger there is that someone might take our spreadsheet and adjust some columns.
So, if this is currently 100 pixels and then they do this.
Well, now it's not a 100 pixels anymore and charts 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 three where I've rewritten that code that 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 where 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, or wherever it 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, I said is, if someone screws with the spreadsheet, they make 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 it is and where I13 is and it makes sure, that the chart fills that space and appears in the correct space every time.
So, I think that's a pretty cool trick there, rather than just hard coding 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.
