Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
I have a somewhat complex set of bar-charts. Some (but not all) of the data
series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
Hi,
You can either write all the values to a variant array and then index that or use the Index function. Dim vntData As Variant vntData = ActiveChart.SeriesCollection(1).Values MsgBox "Point 3=" & vntData(3) MsgBox "Point 3=" & Application.WorksheetFunction.Index( _ ActiveChart.SeriesCollection(1).Values, 3) Cheers Andy David Benson wrote: I have a somewhat complex set of bar-charts. Some (but not all) of the data series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
arrY = ActiveChart.SeriesCollection(i).Values
for p = 1 to ubound(arrY) myPointVal = arrY(p) etc In passing, not sure why you say textboxes are easier to control than Datalabels, I would have thought the opposite irrespective of any complex formats Regards, Peter T "David Benson" wrote in message ... I have a somewhat complex set of bar-charts. Some (but not all) of the data series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
Thanks, Andy! I tried your first suggestion, and it worked great.
-- David "Andy Pope" wrote in message ... Hi, You can either write all the values to a variant array and then index that or use the Index function. Dim vntData As Variant vntData = ActiveChart.SeriesCollection(1).Values MsgBox "Point 3=" & vntData(3) MsgBox "Point 3=" & Application.WorksheetFunction.Index( _ ActiveChart.SeriesCollection(1).Values, 3) Cheers Andy David Benson wrote: I have a somewhat complex set of bar-charts. Some (but not all) of the data series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
Peter T,
Thanks for the help. Your suggestion was essentially duplicated Andy Pope's, and they worked! For what it's worth, I am drawing a chart that shows software size, planned percent complete, and actual percent complete. The software size bars show what percentage of the total software falls into each of the categories on the chart. The planned and actual percent complete bars are scaled so that if percent complete = 100%, the bar is the same height as the corresonding size bar. They are formatted so that they are side-by-side, and both fit within the corresponding total size bar, which obviously is a little more than twice as wide as the percentage bars. The total size bar is royal blue (RGB(0,0,255)), and the plot area is white. If I make the text in the labels black, it doesn't show up well against the dark blue; if I make it white, then obviously it doesn't show up against the plot area (which can happen when percent complete is near 100%). For what it's worth, I inherited the format. It was designed for use with Vice Presidents, so attractiveness of appearance was a major consideration! -- David "Peter T" <peter_t@discussions wrote in message ... arrY = ActiveChart.SeriesCollection(i).Values for p = 1 to ubound(arrY) myPointVal = arrY(p) etc In passing, not sure why you say textboxes are easier to control than Datalabels, I would have thought the opposite irrespective of any complex formats Regards, Peter T "David Benson" wrote in message ... I have a somewhat complex set of bar-charts. Some (but not all) of the data series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the Y-Value of Points
So why not format the labels' font colour to whatever you want, say same as
your textboxes. There are all sorts of "Position" values you can apply, including xlLabelPositionCustom to place them as you would textboxes. In effect you can treat your datalabels as you would textboxes but without the need to update values. Regards, Peter T "David Benson" wrote in message ... Peter T, Thanks for the help. Your suggestion was essentially duplicated Andy Pope's, and they worked! For what it's worth, I am drawing a chart that shows software size, planned percent complete, and actual percent complete. The software size bars show what percentage of the total software falls into each of the categories on the chart. The planned and actual percent complete bars are scaled so that if percent complete = 100%, the bar is the same height as the corresonding size bar. They are formatted so that they are side-by-side, and both fit within the corresponding total size bar, which obviously is a little more than twice as wide as the percentage bars. The total size bar is royal blue (RGB(0,0,255)), and the plot area is white. If I make the text in the labels black, it doesn't show up well against the dark blue; if I make it white, then obviously it doesn't show up against the plot area (which can happen when percent complete is near 100%). For what it's worth, I inherited the format. It was designed for use with Vice Presidents, so attractiveness of appearance was a major consideration! -- David "Peter T" <peter_t@discussions wrote in message ... arrY = ActiveChart.SeriesCollection(i).Values for p = 1 to ubound(arrY) myPointVal = arrY(p) etc In passing, not sure why you say textboxes are easier to control than Datalabels, I would have thought the opposite irrespective of any complex formats Regards, Peter T "David Benson" wrote in message ... I have a somewhat complex set of bar-charts. Some (but not all) of the data series that make up each chart have the values of the points displayed. Due to the complex formatting of the charts, I can't just display the values using Data Labels, so I display them in Text Boxes. I have written a macro that automatically positions each text box just above the bar whose value it shows, and changes the formatting of the text box based on the formatting of whatever it happens to be positioned over. Until now, I got the value associated with each bar from the actual cells on the worksheet. However, a recent change to the requirements for the charts means I am not always plotting data from a contiguous range. For example, a data range might consist of Sheet2!(E2,E7:E10) on one chart, or Sheet 3!(E3:E9) on another chart. This makes looking up the value for each bar in the chart-formatting macro harder. I would like to be able to use the set of plotted values rather than reading cells off the worksheet, but I can't figure out how to get at them. I tried using ActiveChart.SeriesCollection(i).Points(j), but there is no Values property associated with this object. I also tried using ActiveChart.SeriesCollection(i).Values, but that apparently only returns the array of plotted values -- I can't get it to return a single value. Does anyone know how I can return the plotted y-value of a single point? BTW - I am using Excel 2007. As a side comment, it appears to me that the Help function in this version of VBA is seriously degraded from what it used to be in Excel 2003! David Benson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Format numbers from percentage points to basis points | Excel Discussion (Misc queries) | |||
How do I find points on a curve between known points? | Excel Worksheet Functions | |||
finding 1st populated cell on a vector with varying starting points | Excel Discussion (Misc queries) | |||
Finding height between high points | Excel Worksheet Functions | |||
10 X 10 grid and finding distances from multiple points... | Excel Programming |