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
**********************

 

[jimww]

I tried all day yesterday to figure this out. Does anyone have any ideas?

Thanks
Jim

 

[jimww]

I just tried the following code and the .DashStyle and .Weight are working but the color of the trendline is black and I cannot seem to get it be be green.

Does anyone have any ideas?

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

'This code work for setting the weight but so far I can't get the color I want.
With .Trendlines(1).Format.Line
.DashStyle = msoLineLongDashDotDot
.ForeColor.RGB = RGB(0, 150, 0)
.Weight = 10
End With

 

[mikerickson]

Why not create a Series that draws the line?

 

[jimww]

Not sure if I know how to do that gracefully.

 

[jimww]

Well, I've spent all of this morning looking for ways to specify the trendline color. Can't find it in Excel help, can't find it online. I recorded a simple macro of plotting 10 x,y pairs in which I created a trendline and specified the parameters (color and line style) and no info was recorded pertaining to those parameters. I suppose this was a harder problem to do in Excel VBA than I thought.

Thank you mikerickson for your suggestion. However, I have not been able to get a new series plotted.

 

[Jon Peltier]

You're adding a new series with points {0,0} and {1,1}. Forget the trendline. Just format this series so it has a line and no markers (or a circle, as you had). Then apply a fill color that makes sense.

You'll recognize some of this code:

Sub Add45DegreeLine()
  With ActiveChart.SeriesCollection.NewSeries
    .XValues = Array(0, 1)
    .Values = Array(0, 1)
    .Name = "UnitTTTplot"
    .MarkerStyle = xlMarkerStyleCircle
    .MarkerSize = 3
    .MarkerForegroundColor = RGB(0, 150, 0)
    .MarkerBackgroundColor = RGB(0, 150, 0)
    With .Border
      .Color = RGB(0, 150, 0)
      .Weight = xlThin
    End With
  End With
End Sub

 

[jimww]

Jon,

Your solution is spot on. It worked perfectly and I really appreciate your help. I did finally find a way to change the trend line using

UnitTTTChtObj.Chart.SeriesCollection(2).Border.Color = RGB(0, 190, 0)

that I found on the web. I would have never thought that changing a plot line color would have a descriptor as "Border". However, it works.

I am using your method because I think it is a more eligant solution.

Thanks much
Have a great weekend
Jim

 

[Jon Peltier]

Jim -

If you look closely, you'll see that I'm also modifying the .Border.Color of the series. It's broken down within the two With/End-With blocks.

 

[jimww]

Yes, I see that it is also modifying the .Border and I thank you again for your post. I looked for hours to find how to do this in the help and online. I have never programmed anything in Excel and I have a lot of learning to do.

Thanks again so much
Have a great weekend
Jim