If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Min and Max values of X for all series
Users will be plotting small and large values for X and I want to adjust the
X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#2
|
|||
|
|||
Min and Max values of X for all series
Hi Larry,
Have a look at Jon Peltier's example, which includes a reference to Tushar Mehta's Autochart manager. (http://peltiertech.com/Excel/Charts/...nkToSheet.html) L Mehl wrote: Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
|
|||
|
|||
Min and Max values of X for all series
Andy -
Thanks for the plug. Larry - To answer your immediate question, this is how to get the min and max of a series' XValues: worksheetfunction.min(activechart.SeriesCollection (1).xvalues) worksheetfunction.max(activechart.SeriesCollection (1).xvalues) Also, I'd probably use ActiveChart.SeriesCollection.Count as the upper end of my counter, but you probably have something in mind. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Andy Pope wrote: Hi Larry, Have a look at Jon Peltier's example, which includes a reference to Tushar Mehta's Autochart manager. (http://peltiertech.com/Excel/Charts/...nkToSheet.html) L Mehl wrote: Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#4
|
|||
|
|||
Min and Max values of X for all series
Andy and Jon --
Thank you for your continued help. Also, thanks again to all the others who have helped me move this project along. I am very grateful for the amazingly knowledgeable and generous sources in this group. Jon was being very kind when he supposed that I had "something in mind". Truth: I was so happy to get the count that I stopped there. My goal is to get the end value for a loop through all the chart's plotted series. If you have time, can you tell me the difference between your suggestion and my use of ActiveChart.Legend.LegendEntries.Count? Now, onward to Min and Max. Larry "L Mehl" wrote in message news Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#5
|
|||
|
|||
Min and Max values of X for all series
Jon, Andy, et. al. --
I'm back for more help. My loop through chart's series is: Worksheets("Plots").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select For bytLoopValue = 1 To ActiveChart.SeriesCollection.Count douXValueMax = WorksheetFunction.Max( _ ActiveChart.SeriesCollection(bytLoopValue).XValues ) ... The first time through the loop I get the error "Type mismatch" on the last line above. Same error results when I paste ?WorksheetFunction.Max(ActiveChart.SeriesCollectio n(bytLoopValue).XValues) in the Immediate window. The Max function requires a range for its argument. I would have thought the ?WorksheetFunction ... in the Immediate window would result in a range. Did I take you too literally, and leave something out? Thanks in advance for any additional help. Larry "Jon Peltier" wrote in message ... Andy - Thanks for the plug. Larry - To answer your immediate question, this is how to get the min and max of a series' XValues: worksheetfunction.min(activechart.SeriesCollection (1).xvalues) worksheetfunction.max(activechart.SeriesCollection (1).xvalues) Also, I'd probably use ActiveChart.SeriesCollection.Count as the upper end of my counter, but you probably have something in mind. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Andy Pope wrote: Hi Larry, Have a look at Jon Peltier's example, which includes a reference to Tushar Mehta's Autochart manager. (http://peltiertech.com/Excel/Charts/...nkToSheet.html) L Mehl wrote: Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#6
|
|||
|
|||
Min and Max values of X for all series
Larry -
The max function doesn't need a range, it just needs some values. VBA returns an array in the code we've been using. This worked without error for me: Sub AboutTheChart() Dim i As Integer Dim msg As String For i = 1 To ActiveChart.SeriesCollection.Count With ActiveChart.SeriesCollection(i) msg = "series " & i & ": " & .Name & vbLf msg = msg & "Xmax = " & WorksheetFunction.Max(.XValues) & vbLf msg = msg & "Ymax = " & WorksheetFunction.Max(.Values) MsgBox msg, , "FYI" End With Next End Sub On a scatter chart, it does what you expect. If I run it on a Line chart with text category values, the max is 0, since it's text, not a number. Even if I run it on a chart with undefined XValues =SERIES(Sheet1!$C$8,,Sheet1!$C$9:$C$13,1) it returns the number of points, since Excel just uses 1, 2, 3, etc. for the X values in this case. Your error was type mismatch: check how bytLoopValue and douXValueMax are declared. In the immediate window, try this: ?WorksheetFunction.Max(ActiveChart.SeriesCollectio n(1).XValues) Eventually, you don't need all that activating and selecting, either. You can use this Dim myChart as Chart Set myChart = Worksheets("Plots").ChartObjects("Chart 1").Chart For bytLoopValue = 1 To myChart.SeriesCollection.Count ' etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Jon, Andy, et. al. -- I'm back for more help. My loop through chart's series is: Worksheets("Plots").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select For bytLoopValue = 1 To ActiveChart.SeriesCollection.Count douXValueMax = WorksheetFunction.Max( _ ActiveChart.SeriesCollection(bytLoopValue).XValues ) ... The first time through the loop I get the error "Type mismatch" on the last line above. Same error results when I paste ?WorksheetFunction.Max(ActiveChart.SeriesCollectio n(bytLoopValue).XValues) in the Immediate window. The Max function requires a range for its argument. I would have thought the ?WorksheetFunction ... in the Immediate window would result in a range. Did I take you too literally, and leave something out? Thanks in advance for any additional help. Larry "Jon Peltier" wrote in message ... Andy - Thanks for the plug. Larry - To answer your immediate question, this is how to get the min and max of a series' XValues: worksheetfunction.min(activechart.SeriesCollection (1).xvalues) worksheetfunction.max(activechart.SeriesCollection (1).xvalues) Also, I'd probably use ActiveChart.SeriesCollection.Count as the upper end of my counter, but you probably have something in mind. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Andy Pope wrote: Hi Larry, Have a look at Jon Peltier's example, which includes a reference to Tushar Mehta's Autochart manager. (http://peltiertech.com/Excel/Charts/...nkToSheet.html) L Mehl wrote: Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#7
|
|||
|
|||
Min and Max values of X for all series
Someone can delete the legend or individual legend entries, which either
gives you an error or an inaccurate count. "Something in mind" meant maybe you eliminated some legend entries yourself and were trying to consider only the series with visible legend entries. This also is fraught with peril (it's Excel, don't forget), because there's no direct link between the legend and the series. You can loop through the legend entries, but if you've deleted the first, the new first one corresponds to the second series. You can't get the legend entry text either to compare series names, and if there were duplicate names, this would fall down anyway. It would be nice if the legend entry were a property of the series, and .visible one of its properties. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Andy and Jon -- Thank you for your continued help. Also, thanks again to all the others who have helped me move this project along. I am very grateful for the amazingly knowledgeable and generous sources in this group. Jon was being very kind when he supposed that I had "something in mind". Truth: I was so happy to get the count that I stopped there. My goal is to get the end value for a loop through all the chart's plotted series. If you have time, can you tell me the difference between your suggestion and my use of ActiveChart.Legend.LegendEntries.Count? Now, onward to Min and Max. Larry "L Mehl" wrote in message news Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
#8
|
|||
|
|||
Min and Max values of X for all series
Jon --
Thank you for the explanation. I forgot about being able to delete a legend and still have the plot on the chart. Larry "Jon Peltier" wrote in message ... Someone can delete the legend or individual legend entries, which either gives you an error or an inaccurate count. "Something in mind" meant maybe you eliminated some legend entries yourself and were trying to consider only the series with visible legend entries. This also is fraught with peril (it's Excel, don't forget), because there's no direct link between the legend and the series. You can loop through the legend entries, but if you've deleted the first, the new first one corresponds to the second series. You can't get the legend entry text either to compare series names, and if there were duplicate names, this would fall down anyway. It would be nice if the legend entry were a property of the series, and .visible one of its properties. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ L Mehl wrote: Andy and Jon -- Thank you for your continued help. Also, thanks again to all the others who have helped me move this project along. I am very grateful for the amazingly knowledgeable and generous sources in this group. Jon was being very kind when he supposed that I had "something in mind". Truth: I was so happy to get the count that I stopped there. My goal is to get the end value for a loop through all the chart's plotted series. If you have time, can you tell me the difference between your suggestion and my use of ActiveChart.Legend.LegendEntries.Count? Now, onward to Min and Max. Larry "L Mehl" wrote in message news Users will be plotting small and large values for X and I want to adjust the X scale as the plotted data changes. My approach is to - loop through all series on the chart - read the X data range for a series - get the min and max values - reset the values if a lower min value and/or higher max value are found in the next series Is this the best way to do it? If so, can someone help me with the code for reading the X value range for a plotted series inside the loop? For bytLoopValue = 1 To ActiveChart.Legend.LegendEntries.Count strRangeValueX = ... Next bytLoopValue If not can you tell me a more efficient way? Thanks for any help. Larry Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004 |
Thread Tools | |
Display Modes | |
|
|