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/ChartsH...kChartVBA.html and
http://peltiertech.com/WordPress/200...ecorded-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
_______
"Mike" <xx@cxmcast dot net> wrote in message
news:%(E-Mail Removed)...
> 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.
>
Similar Threads
