Chart PageSetup Size Speed VBA

[Brian Reilly, MVP]

Hi folks,

I am completing an Excel VBA project that creates custom chart sizes
from a Library of existing pre-formatted charts by using the Move or
Copy Sheet method from the Library file to the current "datafile" (two
separate files. No problems so far.

I am sizing for PPT and various sizes for Word. Two word sizes require
me to reset the Margins to make the page big enough for me to fit a
ChartArea on it at the correct size (measured in Points that are
translated from Inches). This all works but the following function for
re-sizing slows me down in the following code

Public Function Word_PageSetup_FullPage_Horizontal(wkChart As
Excel.Chart)
'6.75" wide x 7.5" high

'slows down here

With wkChart.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
End With

'speed fine here

wkChart.Activate
With wkChart.ChartArea

.Width = 486
.Height = 540

End With

Is there a faster way to do this? Is the PageSetup a sheetspecific
size or if I reset the PageSetup in the Library file, would that copy
over at the right size with the right margins. Doesn't seem to for me.
Note this code works perfectly and gets me the exactly correct chart
sizes. But takes 3-5 seconds to run just this.

Thanks to all my friends over here.

Brian Reilly, PPT MVP

 

[Jon Peltier]

Hi Brian -

The Excel/VBA implementation of PageSetup is a notorious dog. For critical
page setup functionality, people use the old fashioned XLM technique.

- Jon

 

[Jim Cone]

As Jon suggested, I've also used the xl4 code to speed up pagesetup.
It is faster, but not dramatically so. The learning curve was steep for me.
For what it's worth, something you can try out is
eliminating the InchesToPoints conversion (72 points per inch) ...

..LeftMargin = 18
'and so on.

 

[Brian Reilly, MVP]

Jon and Jim,
Thanks XL4 code is not going to work for me since this has to be
digitally signed and Verisign chokes on XL4 code.

I've got "most" of my charts not needing this so I'm restricting the
PageSetup code to the infrequently used charts. And as slow as it is,
it is still a heap lot faster then what they are doing now. I'll see
what I can do in next version (g).

Brian Reilly, PPT MVP

 

[Brian Reilly, MVP]

Jon and Jim,

Stupid, short-sited me! I am just copying the worksheet and chartsheet
from a static Library file to my new current data file workbook. Why
was I trying to resize that in that current data file workbook. Idiot
(that'd be me). I just resized the buggers in the Library file to
biggest size possible. Only had to run that once. Never again.

May not work for your solutions since I am just copying the chartarea
to PPT and resizing the PPT page size to that size and then exporting
as .wmf

But is working for me right now.