What are the variable types for MaxVal, MinVal, m & A for those of us using Option Explicit?

Hello.

Here’s an alternative way to accomplish the same:

Sub Max_Min_Chart_Point_Values()
 
  Dim sSeriesValueRange As String
  Dim dblMaxVal As Double
  Dim dblMinVal As Double
  Dim lngSeries As Long
  Dim i As Long
 
  dblMinVal = 1.79769313486231E+308
  dblMaxVal = dblMinVal * -1
  lngSeries = ActiveChart.SeriesCollection.Count

  For i = 1 To lngSeries
      sSeriesValueRange = Split(ActiveChart.SeriesCollection(i).Formula, “,”)(2)
      With WorksheetFunction
        dblMaxVal = .Max(Range(sSeriesValueRange), dblMaxVal)
        dblMinVal = .Min(Range(sSeriesValueRange), dblMinVal)
      End With
  Next i
 
  Debug.Print dblMaxVal
  Debug.Print dblMinVal

End Sub
</pre>

This will only work if the data series for the active chart are based on worksheet ranges and not array constants (i.e. almost every chart).

However, there are some advantages. Object inspection is one of the slowest activities that VBA does. Admittedly, most charts do not have a lot of data points, but some do. The technique shown in this comment avoids object inspection almost completely, instead it uses Excel’s built-in MAX and MIN worksheet functions.  All variables are declared and typed, and no objects are left in memory after the procedure finishes. It’s clean and very fast, even with monster-sized charts.

1.79769313486231E+308 is the largest value that VBA can represent using the DOUBLE variable type.

Regards,

Daniel Ferry
http://excelhero.com/blog

You can also use

MaxVal = WorksheetFunction.Max(ActiveChart.SeriesCollection(m).Values)

and

MinVal = WorksheetFunction.Min(ActiveChart.SeriesCollection(m).Values)

Oh yeah, it should work for Y values for any chart type. X values are different, given that they can be text labels.

Jimmy -

MinVal and MaxVal are Doubles, m is a Long, and A is a Variant. (There is no benefit to using Singles and Integers in place of Doubles and Longs.)

@Jon—those were my guesses, except for the Double. I assumed it was a Long type but I don’t really know the expected values. It makes sense.

@Daniel—
“Object inspection is one of the slowest activities that VBA does. “

So is interacting with the worksheet, which is what the WorksheetFunction does.

Also, why are you setting the initial value of the variable that holds the minimum value (dblMinVal) to the LARGEST value a Double can hold? Shouldn’t it be set to the smallest value?

Daniel, JP, and Jon - thanks for the feedback.

JP brings up a good point - it’s always a good idea to declare your variables.  Although I failed to do it here, in practice I always use Option Explicit.

Object inspection isn’t as slow as object modification.

Daniel - Why parse the series formula to get the range? Why not just use the array of values in .Values? This takes care of ranges and literal arrays.

Jimmy - If you set the min to the smallest possible value of a double, likely none of your plotted values will be lower than (or even close to) this minimum.

In any case, the first value varies from the extreme value, so I usually set the initial min and max to the first value, then loop from 2 to N.

@Jon—You’re right, except in Daniel’s function, he’s setting dblMinVal (the variable used to hold the minimum value) to the LARGEST possible value a Double can hold, not the SMALLEST possible value. Or am I misreading something.

@JP-

The logic is correct.  You need to reverse the MAX and MIN in the beginning. Think about it this way: if you want to find the smallest value in your set, you better not start with the smallest value that can exist in any set, because none of the values in your set will be smaller.

On my blog (and everywhere else) I always promote trying to minimize VBA interaction with a worksheet. But in this case, two worksheet interactions for each series (min and max) is far better than two object inspections for every point in every series! What if each series had anywhere near the maximum 32,000 data points? I think two worksheet interactions is a far superior idea.

Jon, you are correct about using the array of values in .Values. I should have thought of that. But I can tell you that my mind was on this strained idea for getting the xVals:

http://support.microsoft.com/kb/914813

that I had seen the other day. I was thinking why would you use such complicated parsing when the Split function does it in one step?

Regards,

Daniel Ferry
excelhero.com/blog

@Daniel—

Thank you, the reversed names confused me. And if you want to avoid the standard loop to get the min or max of a set of values, you need to start with the lowest and highest possible values. Gotcha.

Daniel -

You’re not inspecting every point in every series. You are in one statement putting all of the values for a series into an array, then you’re inspecting array values within the loop.

I did a bunch of tests in Excel 2003 which I’ll blog about next week. The upshot is, it doesn’t matter if you loop through an array or use .Min and .Max; the differences are lost in the scatter. If you load a range into an array, it takes a bit longer than loading the series values into an array, but the difference is less than a factor of 2. Fastest of all is doing .Max(range) and .Min(range) as opposed to .Max(array) and .Min(array), but it’s less than a factor of 2 faster than processing series values. Given that a series may use an array of values rather than a range, I think I’ll stick to using the series values.

Daniel -

That Microsoft article has been around since Excel 97, probably even Excel 95. Split() didn’t arrive in VBA until Excel 2000, and MacExcel VBA still doesn’t have Split().