Specifying Color in VBA Plot

[jimww]

Hello Forum,
I am writing VBA code to make a scatterplot of data contained in two
arrays dim As Double. Along with the points that are plotted
I would like to have a line plotted from coordinates (0,0) to (1,1).
The only way that I know of to create this line is to use TrendLine
and specify as xlLinear. So far i have the plots of the points and
the trendline but I would like to have the trendline as green
instead of the default black. I cannot find the parameter to
change to make this happen. I have looked everywhere that I know
to look and can't find anything. My last hope was to record a
macro of creating the chart I want but none of the line
formatting commands were recorded for some reason. So I am stumped.
Also, I would like to specify major and minor gridlines on the
plot and any help with that would be appreciated.
My apologize for the inefficient code. This is my first VBA
project.
I would supply a graphic of the plot I have now and one of
how I would like it to be but I cannot upload any images.
Thanks for the help
Jim
********************************************
scalednumfail and scaledw Dim As Double and passed to subroutine
Sub UnitTTTplotsub(scalednumfail, scaledw)
Dim UnitTTTChtObj As ChartObject
Dim UnitTTTSeries As Series
Set UnitTTTChtObj = ActiveSheet.ChartObjects.Add _
(Left:=586, Width:=400, Top:=560, Height:=300)
UnitTTTChtObj.Chart.ChartType = xlXYScatter
'Delete other series so only the desired data will plot
'With UnitTTTChtObj
'Do Until Chart.SeriesCollection.Count = 0
'Chart.SeriesCollection(1).Delete
'Loop
'End With
Set UnitTTTSeries = UnitTTTChtObj.Chart.SeriesCollection.NewSeries
With UnitTTTSeries
.XValues = scaledw
.Values = scalednumfail
.Name = UnitTTTplot
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 3
End With
With UnitTTTChtObj.Chart
.HasTitle = True
.ChartTitle.Text = "Unit TTT Plot"
.HasLegend = False
With UnitTTTChtObj.Chart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = "Scaled wi"
End With
With UnitTTTChtObj.Chart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = "Scaled Failure i/(n-1)"
End With

'Add additional series for unit square slope equal 1 for comparing appropriateness of model
Set UnitTTTSeries = UnitTTTChtObj.Chart.SeriesCollection.NewSeries
With UnitTTTSeries
.Trendlines.Add Type:=xlLinear
.XValues = Array(0, 1)
.Values = Array(0, 1)
.Name = UnitTTTplot
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 3
End With

End With
End Sub
**********************

 

[Jon Peltier]

What version of Excel are you using? The macro recorder is very helpful, though in Excel 2007 it doesn't work so well with charts and shapes. I don't even remember a lot of this stuff, so very often I'll whip up a quick dummy chart, record a macro, and extract the syntax I need.

It's not what you know, it's what you know how to find.

 

[jimww]

Jon,

I am using Excel2007 and it's too bad the recorder doesn't work well for charts etc. I have a copy of John Walkenbach's book "Excel 2007 Power Programming with VBA" on reserve at the bookstore. I plan on picking a copy of it up along with a VBA for dummies.

Hopefully, I can come up to speed quickly. All of my programming experience is in other languages i.e. Fortran, Mathematica, SAS and a couple of others. The main frustration is that I know what I want to do but just can't seem to find the right code combo to get it done.

I have read for hours in the help file that comes with Excel and oftentimes it will give a definition of an object, method etc but I have trouble with implementation so more examples would be nice.

I have had an opportunity to check out your website and your code examples have helped me more than anything else. Thank you for posting them on your site.

Thank you Jon,
Jim

 

[Jon Peltier]

Don't waste too much time with Microsoft's help files. They get worse with each successive version of Office.

Use Google. With a little practice you'll get good at picking effective keywords. Remember, it's not what you know, it's what you know how to find out.

 

[jimww]

Thank you Jon. I will remember that.

Jim