Min and Max values of X for all series

[L Mehl]

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

 

[Andy Pope]

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/AxisScaleLinkToSheet.html)

 

[Jon Peltier]

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

 

[L Mehl]

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]

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.SeriesCollection(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]

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.SeriesCollection(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]

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

 

[L Mehl]

Jon --

Thank you for the explanation.

I forgot about being able to delete a legend and still have the plot on the
chart.

Larry