Chart difference between Excel 2003 and Excel 2007

[Mike]

Hi -

I have a script I use to create a scatter chart in Excel 2003. The script
basically creates the chart, sets it to scatter, makes it an object of the
current sheet, sets titles, formats scales (tick labels), finally, rotates
the x-axis tick labels by 90 degrees to make them more readable (they are
fairly long with a format of "m/d/yyyy hh:mm").

In Excel 2003, this all works fine. The last bit, where it rotates the
x-axis labels, the plot area automatically resizes, and the axistitle gets
moved to below the tick labels.

In Excel 2007, the script works differently. First, when it puts in the
ticklabels for the x-axis, in 2003 each label wrapped so they did not
interfere with each other (even though they didn't look great, they were
readable). Now, they don't wrap, and they overwrite each other, resulting
in a mess. Then when the labels are rotate, the plot area does not resize,
and the labels then extend beyond the bottom of the chart (hence are
truncated) and the axistitle conflicts with the ticklabels!

I have included a small csv format of some test data, and a VBA script that
I use. To test, just copy both parts into the appropriate areas of a
spreadsheet, select the data from A1 - E7, run the script "CreateChart".
If you want, set a breakpoint, and step through.

Data:

Date-Time,8084_FreeMB,8085_FreeMB,8086_FreeMB,8087_FreeMB
01/28/200806:00,1200.00,600.00,800.00,550.00
01/28/200818:00,1100.00,700.00,900.00,200.00
01/29/200806:00,1000.00,800.00,700.00,400.00
01/29/200818:00,900.00,500.00,500.00,300.00
01/30/200806:00,1100.00,300.00,400.00,450.00
01/30/200818:00,1150.00,350.00,300.00,500.00

Script:

Options Explicit
Sub CreateChart()

Dim sSheet As String

sSheet = ActiveSheet.Name

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Free Memory" & " " & sSheet
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date/Time
(UT)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Free Memory
(MB)"
End With

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "m/d/yyyy hh:mm"
With Selection.TickLabels
.ReadingOrder = xlContext
.Orientation = 90
End With

End Sub

Any help would be appreciated.

 

[Jon Peltier]

Obviously you need to readjust the size and position of chart elements. I
recorded a macro while resizing the plot area to bring the axis labels into
view and while moving the axis title down below the labels. The relevant
lines of code are:

ActiveChart.PlotArea.Height = 86
ActiveChart.Axes(xlValue).AxisTitle.Top = 201

This led to an unexpected movement of the wrong axis, and the discovery of a
bug with the macro recorder. In an XY chart, both the X and Y axes are value
axes (i.e., not category axes), even though the X axis is always in VBA
referred to as the xlCategory axis. Whoever coded the recorder inserted the
xlValue parameter instead of the xlCategory parameter when the X axis title
was moved. I've filed the bug report.

The relevant lines of code instead are:

ActiveChart.PlotArea.Height = 86
ActiveChart.Axes(xlValue).AxisTitle.Top = 201

Insert them before End Sub in your routine. You may decide to change the
size and shape of the chart, and use different values where I have 86 and
201 in these lines.

You can further streamline your procedure (see
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html and
http://peltiertech.com/WordPress/2008/03/11/how-to-fix-a-recorded-macro/ for
more hints):


Sub CreateChart()

Dim sSheet As String
Dim cht As Chart

sSheet = ActiveSheet.Name

Charts.Add

Set cht = ActiveChart.Location(Where:=xlLocationAsObject, Name:=sSheet)
With cht
.ChartType = xlXYScatter
.HasTitle = True
.ChartTitle.Characters.Text = "Free Memory" & " " & sSheet
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
With .TickLabels
.AutoScaleFont = True
With .Font
.Name = "Arial"
.Size = 8
End With
.NumberFormat = "m/d/yyyy hh:mm"
.ReadingOrder = xlContext
.Orientation = 90
End With
With .AxisTitle
.Characters.Text = "Date/Time (UT)"
.Top = 201
End With
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
With .TickLabels
.AutoScaleFont = True
With .Font
.Name = "Arial"
.Size = 8
End With
End With
.AxisTitle.Characters.Text = "Free Memory (MB)"
End With

.PlotArea.Top = 35
.PlotArea.Height = 98

End With

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______