Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost arrow functionality within Excel | Excel Discussion (Misc queries) | |||
form with excel and word functionality | Excel Discussion (Misc queries) | |||
OS Version impact on Excel functionality | Excel Worksheet Functions | |||
Template functionality in Excel | Excel Discussion (Misc queries) | |||
Easiest way to add functionality to ALL Excel spreadsheets | Excel Programming |