Page Setup macro taking forever in Excel 2007

[pam53154]

I had a standard page setup macro in Excel 2003 (set margins, footers, etc the way I like them). However, when I try running the same type of macro in 2007, it takes so long that it is quicker to just do the formatting myself. Any ideas? Thanks.

 

[Smitty]

Welcome to the Board!

Page Setup code is notorioulsy slow.

Your best bet is to go into it and remove all of the default statements, just sticking with those elements you're actually applying/changing.

Hope that helps,

 

[pam53154]

Thanks! I'll give that a try.

 

[mikejvir]

I have a similar problem but only in Excel 2003. Depending on how my computer feels (lack of any other way to describe it) my macro can take anywhere from 15 seconds to I have to abort Excel. Any ideas what might cause this? (When I have a long time, I shut down all other applications). Here is my code which is used to create the information on a Chart Tab.

Thanks for your help.

Mike Virostko


Sub Reformat_Chart_page()
'
' Reformat_Chart_page Macro
' Macro recorded 1/27/2006 by Michael J. Virostko
'
' Keyboard Shortcut: Ctrl+o
'
' Set up Overall Page size along with the Header and Footers
'
ActiveWindow.Zoom = 100

With ActiveChart.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = _
"&""Times New Roman,Bold""XXXXXXXX Corporation" & Chr(10) & "&""Times New Roman,Regular""&8&F - &A"
.CenterFooter = ""
.RightFooter = "&""Times New Roman,Bold""&10Page &P" & Chr(10) & "Printed " & "&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.ChartSize = xlFullPage
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
'
' Define the area for the plot
'
ActiveChart.PlotArea.Select
Selection.Left = 25
Selection.Width = 695
Selection.Top = 50
Selection.Height = 450
'
' Set up graph to have only major gridlines
'
ActiveChart.PlotArea.Select
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
'
' Set up major gridlines to be a dashed line
'
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
'
' xlCategory represents x
' xlvalue represents y
'
' Format the y-axis
'
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
' .MinimumScale = 11.4
' .MaximumScale = 14.9
' .MinorUnit = 0.05
' .MajorUnit = 0.5
.Crosses = xlCustom
.CrossesAt = -200
' .ReversePlotOrder = False
' .ScaleType = xlLinear
' .DisplayUnit = xlNone
End With

With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlNextToAxis
End With
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.NumberFormat = "0.0"
'
' Format the x-axis
'
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlLow
' .MinimumScale = 0.6
' .MaximumScale = 1.2
' .MinorUnit = 0.01
' .MajorUnit = 0.05
.Crosses = xlCustom
.CrossesAt = -200
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.NumberFormat = "0.0"
' Selection.TickLabels.NumberFormat = "0.00"
' Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
'
' Flormat the plot area for white background, solid border.
'
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 1
.Weight = xlMedium
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
'
' Format the Chart Title
'
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

ActiveChart.Deselect
'
' Reformat the various titles
'
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With

End Sub

 

[steimel386]

Anything with a false or default setting can be deleted out. You don't have to tell it to do something that it already does automatically. If nothing else, it should make it easier to get around in for editing. I'm sure someone else will be able to pare this down even more.

Sub Reformat_Chart_page()
'
' Reformat_Chart_page Macro
' Macro recorded 1/27/2006 by Michael J. Virostko
'
' Keyboard Shortcut: Ctrl+o
'
' Set up Overall Page size along with the Header and Footers
'
ActiveWindow.Zoom = 100

Application.ScreenUpdating = False
Application.EnableEvents = False 
 
With ActiveChart.PageSetup
.LeftFooter = _
"&""Times New Roman,Bold""XXXXXXXX Corporation" & Chr(10) & "&""Times New Roman,Regular""&8&F - &A"
.RightFooter = "&""Times New Roman,Bold""&10Page &P" & Chr(10) & "Printed " & "&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.ChartSize = xlFullPage
.PrintQuality = 600
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.Zoom = 100
End With
'
' Define the area for the plot
'
ActiveChart.PlotArea.Select
Selection.Left = 25
Selection.Width = 695
Selection.Top = 50
Selection.Height = 450
'
' Set up graph to have only major gridlines
'
ActiveChart.PlotArea.Select
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
'
' Set up major gridlines to be a dashed line
'
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
'
' xlCategory represents x
' xlvalue represents y
'
' Format the y-axis
'
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
' .MinimumScale = 11.4
' .MaximumScale = 14.9
' .MinorUnit = 0.05
' .MajorUnit = 0.5
.Crosses = xlCustom
.CrossesAt = -200
' .ReversePlotOrder = False
' .ScaleType = xlLinear
' .DisplayUnit = xlNone
End With

With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlNextToAxis
End With
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
Selection.TickLabels.NumberFormat = "0.0"
'
' Format the x-axis
'
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlLow
' .MinimumScale = 0.6
' .MaximumScale = 1.2
' .MinorUnit = 0.01
' .MajorUnit = 0.05
.Crosses = xlCustom
.CrossesAt = -200
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
Selection.TickLabels.NumberFormat = "0.0"
' Selection.TickLabels.NumberFormat = "0.00"
' Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
'
' Flormat the plot area for white background, solid border.
'
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 1
.Weight = xlMedium
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
'
' Format the Chart Title
'
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
End With

ActiveChart.Deselect
'
' Reformat the various titles
'
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 14
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[mikejvir]

Hello Thanks for the reply.

I have tracked the problem down to the ActiveChart.PageSetup
I have done the following to this section but the macro still runs in 45 to 50 seconds with the rest taking a couple of seconds.

    With ActiveChart.PageSetup
'        .LeftHeader = ""
'        .CenterHeader = ""
'        .RightHeader = ""
        .LeftFooter = _
        "&""Times New Roman,Bold""Hittite Microwave Corporation" & Chr(10) & "&""Times New Roman,Regular""&8&F - &A"
'        .CenterFooter = ""
 '       .RightFooter = "&10Page &P" & Chr(10) & "Printed " & "&D"
        .RightFooter = "&""Times New Roman,Bold""&10Page &P" & Chr(10) & "Printed " & "&D"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.75)
'        .HeaderMargin = Application.InchesToPoints(0.5)
'        .FooterMargin = Application.InchesToPoints(0.5)
        .ChartSize = xlFullPage
'        .PrintQuality = 600
'        .CenterHorizontally = False
'        .CenterVertically = False
'        .Orientation = xlLandscape
'        .Draft = False
'        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
'        .BlackAndWhite = False
        .Zoom = 100
    End With

Any other ideas to reduce the time? The reason this is becoming an issue is because I use Excel to plot data for customers. This macro, Reformat_Chart_page, is run on every chart I generate and each part requires 16 graphs. I have seen the entire plotting macro work in 4 or 5 minutes. I have at least 10 more parts to due today.

Thanks,

Mike