Macro must run twice, Don't know why

[mikejvir]

Hello,

I am a loss. I wrote a Macro to reformat a Chart Tab in a very specific format. I have two problems. The first is I must run the macro twice for the format to be correct. The plot area is fixed on the second running of the macro.

here is the code in all it's glory and yes it needs to run for any version of Excel starting with 2003 or newer (we use 2003 and 2007).

Option Base 1
Option Explicit
Sub Run_RFMt_DS_Chart()
Dim ExcelVersion As Integer
Dim mlabel As Integer
  mlabel = 1
  ExcelVersion = Application.Version
  Call RF_DS_Chart_Page(ExcelVersion, mlabel)
End Sub

Sub RF_DS_Chart_Page(ExcelVersion As Integer, mlabel As Integer)

Dim ChtType As Variant
Dim borderweight As Variant
'
  borderweight = 2
'
' Reformat_Chart_page Macro
' Macro recorded 1/27/2006 by Michael J. Virostko
' Revised 7/14/2011  for Excel 2007 speed.
' Set up Overall Page size along with the Header and Footers
'
  ActiveWindow.Zoom = 100
  ActiveChart.Activate
  Application.ScreenUpdating = False  ' do all transfers in the background
  Application.DisplayStatusBar = True
'
' Set Print Page Setup
'
  ActiveChart.ChartType = xlXYScatterSmoothNoMarkers  ' Force Scatter graph to me smooth lines
  ChtType = ActiveChart.ChartType
  
  With ActiveChart.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = _
      "&""Times New Roman,Bold""Integrated Device Technologies" & 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
    .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 size and area
' Define the Chart area (no border)
'
  If ExcelVersion >= 12 Then
    ActiveChart.ChartArea.Select
    With Selection
      .Top = 0
      .Left = 0
      .Width = 745  '735
      .Height = 530 '530
      .Border.LineStyle = xlNone
      .Interior.ColorIndex = xlNone
    End With
  End If
'
' Define the area for the Plot Area within the Chart Area
'
  ActiveChart.PlotArea.Select
  With Selection
    .Top = 40
    .Left = 60
    .Width = 685
    .Height = 445
  End With
'
'  Format the plot area for white background, solid border.
'
  ActiveChart.PlotArea.Border.Weight = borderweight + 1
'
' Set up graph to have only major gridlines
'
  With ActiveChart.Axes(xlCategory)
    .HasMajorGridlines = True
    .HasMinorGridlines = False
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    .HasMajorGridlines = True
    .HasMinorGridlines = False
  End With
'
'  Force no Chart Title
'
  ActiveChart.HasTitle = False
'
'  xlCategory represents  x
'  xlvalue    represents  y
'
'  Format the y-axis (xlValue)
'
  If ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False Then
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
  End If
'
' Set up major gridlines to be a continous line
'
  ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Select
  With Selection.Border
    .ColorIndex = 1
    .Weight = 1  ' use a number instead of XL variable
    .LineStyle = xlContinuous 'xlDot
  End With
    
  ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Select
  With Selection.Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 22
    .ColorIndex = 1
    .Background = xlAutomatic
  End With

  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlTop
    .ReadingOrder = xlContext
    .Orientation = xlUpward
    If mlabel <> 0 Then .Left = 10
  End With

  ActiveChart.Axes(xlValue, xlPrimary).Select
  With Selection
    .Crosses = xlCustom
    .CrossesAt = -200
    .Format.Line.Weight = 2 ' xlHairline
    .TickLabelPosition = xlLow
    .Border.Color = RGB(0, 0, 0)
    .Crosses = xlCustom
    .MajorTickMark = xlCross
    .MinorTickMark = xlInside
    .TickLabelPosition = xlNextToAxis
  End With

  With Selection.TickLabels.Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 18
    .ColorIndex = 1
  End With
'
'  Format the x-axis (XlCaterory)
'
  If ActiveChart.Axes(xlCategory).HasTitle = False Then
    ActiveChart.Axes(xlCategory).HasTitle = True
  End If
    
  ActiveChart.Axes(xlCategory).MajorGridlines.Select
  With Selection.Border
    .ColorIndex = 1 ' 57
    .Weight = xlThin
    .LineStyle = xlContinuous 'xlDot
  End With

  ActiveChart.Axes(xlCategory).AxisTitle.Select
  With Selection.Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 22
  End With

  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
    .Orientation = xlHorizontal
    If mlabel <> 0 Then .Top = 480
  End With

  ActiveChart.Axes(xlCategory).Select
  With Selection
    .Crosses = xlCustom
    .CrossesAt = -200
    .Format.Line.Weight = borderweight ' xlThin ' xlHairline
    .TickLabelPosition = xlLow
    .Border.Color = RGB(0, 0, 0)
    .Crosses = xlCustom
    .MajorTickMark = xlCross
    .MinorTickMark = xlInside
    .TickLabelPosition = xlNextToAxis
  End With

  With Selection.TickLabels.Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 18
    .ColorIndex = 1
    .Background = xlAutomatic
  End With
'
' Force Legend to have a NO border, white solid fill.
'
  ActiveChart.Legend.Select
  With Selection.Border
    .Weight = xlThin
    .LineStyle = xlLineStyleNone ' xlContinuous
  End With
  Selection.Shadow = False
  With Selection.Interior
    .ColorIndex = 2
    .PatternColorIndex = 1
    .Pattern = xlSolid
  End With

  Selection.AutoScaleFont = False
  With Selection.Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 14
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 1
    .Background = xlTransparent
  End With
  
  Application.ScreenUpdating = True  ' do all transfers in the background

End Sub

I have search the code to determine why, but to no avail. I need to give this macro to other and I really do not want to add a second running of the sub. If anyone could review this to see what I am missing, I would eternally grateful.

The second issue deals with the axis. Instead of letting Excel determine where the axis is, I do need to move them. The issue is that the titles are not centered along the axis. I thought just moving the category (horizontal) axis down and the value (vertical) axis left would leave the centered, but it does not. Any ideas would be helpful.

Thanks.

Mike Virostko

 

[GlennUK]

I'd recommend that you write 2 sets of code, one for Excel 2003 and the other for Excel 2007. You can switch between the 2 at run time by testing what version of Excel is active, but develop the 2 sets of code in the environment that they are to be used in.

Why do I think this ... because the charting engine of Excel changed so much between Excel 2003 and 2007 that common code is likely to run into problems.

 

[mikejvir]

Hi Glenn,

Thanks for the reply. The code started in 2003. All I added was a parameter for the Excel version. If greater than 12 (Excel 2007) then I define a Chart Area. The plot area is defined for all versions.

Do you think the code for 2003 should be re-written as object models for 2007?

Thanks

Mike Virostko

PS - I like your by-line

 

[GlennUK]

Yes, rewrite the code for 2007, and test to see if it behaves as expected. Any code adjustment really should be done for whatever version the tweak is required for. You will probably find what is caused the need to run it twice while you are doing all of this.