new excel vba functionality

I don't have any clout around here (Rita) but if they did it might look
something like this.

x = Worksheets("sheet1").ChartObjects(1).Chart. _
SeriesCollection(1).Points(3).Value

--
Don Guillett
SalesAid Software
donaldb@281.com
"medicenpringles"
<medicenpringles.1z801c_1133203811.1343@excelforum-nospam.com> wrote in
message news:medicenpringles.1z801c_1133203811.1343@excelforum-nospam.com...
>
> this will probably never get considered or even read, but i'll try
> anyway. I've been developing solutions in Excel for a long time and i
> love everything about it. but one of the things i've run into several
> times is the issue of conditional chart formatting. there are a
> million ways to do this, but i think maybe if some MVP out there could
> drop Microsoft a line with my suggestion, it would help at least me out
> tremenously.
>
> my suggestion is simply this: i think there should be a way to
> programmatically reference specific series points on the chart. that
> way you could write VBA code like:
>
> If myChart.SeriesPoint(1) > myChart.SeriesPoint(2) Then
> myChart.Trendlines(1).Color = Red
> End If
>
> this may be possible with Visual Studio Tools for Office, but i've
> never gotten to touch that yet.
>
> any advice welcome.
>
>
> --
> medicenpringles
>
>
> ------------------------------------------------------------------------
> medicenpringles's Profile:
> http://www.excelforum.com/member.php...o&userid=16458
> View this thread: http://www.excelforum.com/showthread...hreadid=488827
>

If by referencing the points, it appears you mean the values of the points;
this is possible:

Dim MyPoints as Variant
Set MySeries = MyChart.SeriesCollection(1)
Set MyPoints = MySeries.Values
' MyPoints is now an array containing the values for all the points in
MySeries
For i = 1 to UBound(MyPoints)
MsgBox MyPoints(i)
Next i

Many other properties of the points are available through MySeries.Points,
of course.
--
- K Dales


"medicenpringles" wrote:

>
> this will probably never get considered or even read, but i'll try
> anyway. I've been developing solutions in Excel for a long time and i
> love everything about it. but one of the things i've run into several
> times is the issue of conditional chart formatting. there are a
> million ways to do this, but i think maybe if some MVP out there could
> drop Microsoft a line with my suggestion, it would help at least me out
> tremenously.
>
> my suggestion is simply this: i think there should be a way to
> programmatically reference specific series points on the chart. that
> way you could write VBA code like:
>
> If myChart.SeriesPoint(1) > myChart.SeriesPoint(2) Then
> myChart.Trendlines(1).Color = Red
> End If
>
> this may be possible with Visual Studio Tools for Office, but i've
> never gotten to touch that yet.
>
> any advice welcome.
>
>
> --
> medicenpringles
>
>
> ------------------------------------------------------------------------
> medicenpringles's Profile: http://www.excelforum.com/member.php...o&userid=16458
> View this thread: http://www.excelforum.com/showthread...hreadid=488827
>
>

I had prepared a response to your post but K Dales beat me to it. So I'll add
to his post. And a caution that I'm no expert - just someone like yourself.

A series contains a Points collection. Unfortunately, an individual point
does not support a Value property as shown here:

Sub Test0()
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
.HasDataLabels = True
For i = 1 To .Points.Count
MsgBox .Points(i).DataLabel.Text
MsgBox .Points(i).MarkerStyle
'MsgBox .Points(i).Value 'This errors
Next
End With
End Sub

The Values property of a series returns an array of all the point values in
the series. However, there seems to be a bit of a glitch in extracting an
individual element as shown in the following three macros. But to answer your
question: Yes, it is already possible.

'This works
Sub Test1()
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To .Points.Count
MsgBox Application.Index(.Values, i)
Next
End With
End Sub

'This also works
Sub Test2()
Dim arr As Variant
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
arr = .Values
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next
End With
End Sub

'This DOESN'T work
Sub Test3()
Dim i As Long
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
MsgBox IsArray(.Values)
MsgBox LBound(.Values)
MsgBox UBound(.Values)
For i = LBound(.Values) To UBound(.Values)
MsgBox .Values(i) 'Errors here ???
Next
End With
End Sub

Also note that since one usually knows the range address of the chart series
it is generally simple to extract it directly from the range rather than from
the series.

Regards,
Greg

"medicenpringles" wrote:

>
> K Dales Wrote:
> > Many other properties of the points are available through
> > MySeries.Points,
> > of course.
>
> so this is already possible?
>
>
> --
> medicenpringles
>
>
> ------------------------------------------------------------------------
> medicenpringles's Profile: http://www.excelforum.com/member.php...o&userid=16458
> View this thread: http://www.excelforum.com/showthread...hreadid=488827
>
>