The Excel 2010 & 2013 macro recorder will not record how to stack and scale a picture fill in a chart. This episode shows two different sets of code to control the stack and scale settings.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel, Podcast Episode 1821: Chart Stack and Scale Picture VBA Macro.
Hey. Welcome back to MrExcel netcast.
I’m Bill Jelen.
Well, today we have a problem with the macro recorder.
There is a way to take a picture and paste it into the series, and make that stack and scale, but the macro recorder does not record that.
So, here, let's turn on the macro recorder, say, HowToStackAndScale.
Okay.
Alright.
So, there, we’re watching the macro get created.
We're going to click on the picture, CTRL-C to copy, and then click on Series 1 and CTRL-V to paste which takes this picture and stretches it which, in this particular case, doesn't look that good, so I'm going to use CTRL-1 to format the series.
Go to Fill where they offer settings to either stack the picture like that, or I can stack and scale and say that each picture should take up, you know, maybe 20 units in the chart, and click Close.
So, that's what I want the chart to look like, but you see that after I pasted the picture, nothing is getting recorded, so none of the stack and scale items are getting recorded.
That's just one of the things that, you know, back in Excel 2007, the chart macro recorder did not work at all.
Excel 2010, they tried to add a lot of things back but apparently they figured this one was so obscure they just never got around to adding that to the macro recorder.
So, what do we do?
Well, VBA Help makes you think that you need to use this method called UserPicture.
UserPicture lets you specify where to fill the picture from and then the picture format and picture stack unit.
This is all one big command here and so, unfortunately, UserPicture doesn't have a way to paste from the clipboard.
You have to get some sort of a dummy picture, change the stack and scale, the stack unit, and then you can paste your copied picture in.
So, here, let's just jump back to Excel.
You see I went back to the regular fill.
I'll do F8, F8, F8, F8, F8.
Alright.
So, now, this line of code gets run which puts our dummy picture in there, stacks it 1 for every 20, and then when I do the paste, I'm pasting the copied picture in, and so you would think that's the way you have to go but there's actually a faster way.
Alright.
So, in this case, we select the picture to copy, and then, with SeriesCollection(1), we do the paste, and then .PictureType.
Isn’t this frustrating?
Here, they use PictureFormat as the argument but the actual property name is PictureType, and then PictureUnit2, how we are supposed to figure that out, is the PictureStackUnit So, let’s go back here, change that back to a solid blue fill, and then we'll run this macro, F8, F8, F8.
See, we do the paste first which puts it in as a stretch, and change the stack and scale, and then change the PictureUnit to 20, and we are good to go.
Alright.
So, there we have it.
A bug with a macro recorder just doesn't record that code.
If you need to use the stack and scale and change the PictureUnit, the StackUnit, this seems to be the fastest code to use.
Hey. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel for MrExcel, Podcast Episode 1821: Chart Stack and Scale Picture VBA Macro.
Hey. Welcome back to MrExcel netcast.
I’m Bill Jelen.
Well, today we have a problem with the macro recorder.
There is a way to take a picture and paste it into the series, and make that stack and scale, but the macro recorder does not record that.
So, here, let's turn on the macro recorder, say, HowToStackAndScale.
Okay.
Alright.
So, there, we’re watching the macro get created.
We're going to click on the picture, CTRL-C to copy, and then click on Series 1 and CTRL-V to paste which takes this picture and stretches it which, in this particular case, doesn't look that good, so I'm going to use CTRL-1 to format the series.
Go to Fill where they offer settings to either stack the picture like that, or I can stack and scale and say that each picture should take up, you know, maybe 20 units in the chart, and click Close.
So, that's what I want the chart to look like, but you see that after I pasted the picture, nothing is getting recorded, so none of the stack and scale items are getting recorded.
That's just one of the things that, you know, back in Excel 2007, the chart macro recorder did not work at all.
Excel 2010, they tried to add a lot of things back but apparently they figured this one was so obscure they just never got around to adding that to the macro recorder.
So, what do we do?
Well, VBA Help makes you think that you need to use this method called UserPicture.
UserPicture lets you specify where to fill the picture from and then the picture format and picture stack unit.
This is all one big command here and so, unfortunately, UserPicture doesn't have a way to paste from the clipboard.
You have to get some sort of a dummy picture, change the stack and scale, the stack unit, and then you can paste your copied picture in.
So, here, let's just jump back to Excel.
You see I went back to the regular fill.
I'll do F8, F8, F8, F8, F8.
Alright.
So, now, this line of code gets run which puts our dummy picture in there, stacks it 1 for every 20, and then when I do the paste, I'm pasting the copied picture in, and so you would think that's the way you have to go but there's actually a faster way.
Alright.
So, in this case, we select the picture to copy, and then, with SeriesCollection(1), we do the paste, and then .PictureType.
Isn’t this frustrating?
Here, they use PictureFormat as the argument but the actual property name is PictureType, and then PictureUnit2, how we are supposed to figure that out, is the PictureStackUnit So, let’s go back here, change that back to a solid blue fill, and then we'll run this macro, F8, F8, F8.
See, we do the paste first which puts it in as a stretch, and change the stack and scale, and then change the PictureUnit to 20, and we are good to go.
Alright.
So, there we have it.
A bug with a macro recorder just doesn't record that code.
If you need to use the stack and scale and change the PictureUnit, the StackUnit, this seems to be the fastest code to use.
Hey. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
