Chart Formatting Speed is slow

[mikejvir]

Hello All,

I am in the process of having to move from Excel 2003 to Excel 2007. I have one macro that format a chart page (XY Scatter) with headers, footers, chart area, and various plot attributes. In Excel 2003 the macro takes about 2 seconds. In Excel 2007 this macro takes more than 30 seconds. Since I generate many graphs this is unacceptable. In both version, the default printer is set for Adobe PDF so Excel does not have to access the remote spooler. We (I have a colleague who has helped) have gotten it down to 15 seconds by simulating an enter key, but sometimes that fails and I have to manually enter Okay. (This is currently commented out of the code).

Attached is the code (sorry it is so long). But I am looking for any help anyone can give me.

Also, I can not make this the default chart type since I have other colleagues who use it.

Thanks for you time and help.

Mike Virostko

Sub RF_Chart_page()
'
' Reformat_Chart_page Macro
' Macro recorded by Michael J. Virostko
'
' Keyboard Shortcut: Ctrl+o
'
' Set up Overall Page size along with the Header and Footers
'
'Arg1 Header dialog
'Arg2 Footer dialog
'Arg3 left margin
'Arg4 Right margin
'Arg5 Top Margin
'Arg6 Bottom Margin
'Arg7 printed chart size
'Arg8
'Arg9
'Arg10 Page orientation 1= portrate 2= Landscape
'Arg11 paper setting xlPaperLetter
'Arg12 Scaling
'Arg13 First Page
'Arg14 Black and white
'Arg15 Pring Quality
'Arg16 Head margin
'Arg17 foot margin
'Aeg18 Draft quality
    
   ActiveWindow.Zoom = 100
   ActiveChart.Activate

' Print Page Setup

'Application.SendKeys "{ENTER}", False
'Application.Dialogs(xlDialogPageSetup).Show _
'                  Arg2:="&L&""Times New Roman,Bold""My Corporation" & Chr(10) & "&""Times New Roman,Regular""&8&F - &A" & _
'              "&R&""Times New Roman,Bold""&10Page &P" & Chr(10) & "Printed " & "&D", _
'                Arg3:=0.25, _
'                Arg4:=0.25, _
'                Arg5:=0.25, _
'                Arg6:=0.75, _
'                Arg16:=0.5, _
'                Arg17:=0.5, _
'                Arg10:=2, _
'                Arg11:=xlPaperLetter, _
'                Arg12:=100, _
'                Arg14:=False, _
'                Arg15:=600
'
    With ActiveChart.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = _
        "&""Times New Roman,Bold""My 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

'For Excel 2007,  Need to define the Chart Area
'
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Top = 0
    ActiveChart.ChartArea.Left = 0
    ActiveChart.ChartArea.Width = 747  '735
    ActiveChart.ChartArea.Height = 532 '530
    ActiveChart.Deselect
'
' Define the area for the plot, needed only for Excel 2003
'
  If 1 = 1 Then
    ActiveChart.PlotArea.Select
    Selection.Left = 25
    Selection.Width = 695
    Selection.Top = 50
    Selection.Height = 450
    End If
    
'
' Define the Chart area (no border)
'
    ActiveChart.ChartArea.Select
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    Selection.Shadow = False
    Selection.Interior.ColorIndex = xlNone
'
' 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"
'    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
'
'  Format 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
'
' Force Legend to have a border.
'
    ActiveChart.Legend.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    
    Selection.Shadow = False
'    Selection.Interior.ColorIndex = xlNone
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .Background = xlTransparent
    End With
 
 '   ActiveChart.Deselect
    End Sub

 

[Derek Brown]

Excel 2007 does seem to run slower at times - when you select a chart sheet you can usually 'see' it refreshing when the data has changed.
For your code, you could try suspending the screen refeshing:

Application.ScreenUpdating = False
.
.
.
.
Application.ScreenUpdating = True

A Google search for "Excel 2007 chart refresh slow" will bring up several posts of people having similar problems. In particular look at Jon Peltier's comments at:
http://www.pcreview.co.uk/forums/excel-2007-charting-slow-t3144539.html
(By the way, if you haven't already come across it, visit Jon's excellent store of chart ideas at: http://peltiertech.com/Excel/Charts/ChartIndex.html)

 

[mikejvir]

Hi Derek,

Thanks for the reply. Using the Application.ScreenUpdating option took off 1 second (30 to 29 seconds).

I am attempting to determine if my version of Excel 2007 has the hotfix your pointed me to. I do not expect this to fix it, but to hopefully improve the time.

I'd loved to rant about how disgusted I am with Excel 2007, but you and this group probably heard it all already.

Mike Virostko

 

[mikejvir]

Hi Derek,

We determined that I was running the orginal Excel 2007. After upgrading to SP2, the time decreased to 9 seconds. Still not happy, but a vast improvement.

Curious, did you see anything in the way the macro was written that would make it faster? Is there anyway to compile the macro and if so would that make it faster? (This probably dates me).

Mike Virostko

 

[Derek Brown]

Sorry that I cannot help you very much with the VBA as I don't use that very much for charts and I am using Office 2010 on Windows 7 anyway.
However, I wonder about the spec of your machine. I use a system with 4Gb of RAM and a solid-state drive (SSD) so it runs quite fast anyway. I also have a system running Windows Server 2008 R2 and Office 2007 but that runs fast (I use RDP sessions) as it accesses data on the LAN and not over the network. I also have Excel set for multi-threading but there is quite a bit of Excel (especially VBA) that will not use all four processors that I have on my machines. I don't know if Excel 2007 can address 4Gb of RAM.
If your data is being accessed over the network, you could try opening a copy of the workboook on your C: drive to see if the network connection is relevant to the problem.
The link that I gave to Jon's site contains quite a number of articles on using VBA for charts so you may get some ideas there.

 

[mikejvir]

Hi Derek,

It is currently running on a Dell Latitude with 1 CPU, 2GB of RAM, and Windows XP PRO. Data is local to the computer. This is a company laptop. I have a newer laptop (Dell, dual core, 4 GB of RAM, and Windows XP Pro). That might help. I am a causal programmer and I use the macro recorder to help get me started. In 2007 macro recorder is useless and I have to completely understand objects. I'm an old timer (well not that old) and dealt with FORTRAN, BASIC, and C 20 to 30 years ago. Back then you figured out how to live with 0.64 MB (or 640 kB when we talked back then).

I'll keep you update to let you know what I find.

Thanks for all help.

Mike Virostko

 

[Derek Brown]

Ah, the Good Old Days! For me it was mainframe COBOL (1978-2001) and assembler on a ZX Spectrum (with a massive 16Kb of RAM!).
Yes, the macro recorder at 2007 is hopeless but I understand that it is better with Excel 2010, although I have not tried it myself - I don't bother with the macro recorder these days because it doesn't create very efficient code. I do have a comprehensive library of books but if I do need any help/ideas for charts it is to Jon's site that I go.
Although Excel 2010 is much better than 2007, it does present a number of other 'challenges'.
Good luck with your search for the ideal solution.