ExcelBanter - new excel vba functionality

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - new excel vba functionality (https://www.excelbanter.com/excel-programming/346756-new-excel-vba-functionality.html)

new excel vba functionality

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

new excel vba functionality

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

new excel vba functionality

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

new excel vba functionality

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 he

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