Using vba to generate xy scatter plots in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I was trying to use vba to generate some xy scatter plots. It seems
like a simple task, but I haven't been able to find much on it. Anyways, I
have one column of data which I want to be the x-values, and another column
of y-values. I want to set the y-axis to a logarithmic scale, and I want to
be able to specify the maximum and minimum x and y values. Any help would be
greatly appreciated.
 
The quick start is to turn on the macro recorder and build your first chart
by hand. Make all appropriate settings. then turn off the macro recorder and
generalize the recorded macro to suit your needs.

General help can be found at:

http://peltiertech.com/
 
I tried doing that but didn't get very far. When I try to compile the code
from the macro recorder, I get an error "unable to set the values property of
the series class" I don't understand the syntax that's being used to assign
the x and y values. I tried changing it to the cells I wanted to plot, i.e.
J15:J1038, etc, and still get the same error. Thanks. Here's the recorded
macro:

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R15C10:R1038C10"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R15C14:R1038C14"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Aperture"
ActiveChart.Axes(xlValue).MajorGridlines.Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.01
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLogarithmic
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = -100
.MaximumScale = 100
.MinorUnitIsAuto = True
.MajorUnit = 20
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
 
Nevermind.. I had accidentally deleted the columns I was trying to graph from
and that was causing the error. Thanks.
 
Back
Top