How to retrieve the max/min values and locations in Excel Chart object

Question

How do I retrieve the max and min values of an excel chart series collection as well as the position of the same on the chart? I want to calculate where the label should be for each point in the series and move it accordingly.

I'm doing this to make them look nice, in case your wondering. The numbers tend to get all messed up if I just blindly move the labels. I'm sure I could have the number at the base of the chart, but that's not what I'm trying to do. It doesn't help much that the Chart object is hard to get to work with the Object Listing thing-a-ma-jig (that whatcha-ma-call-it that pops up when you hit period after an object name).

Edit: What I'm trying to achieve is the retrieval of the highest(max) and lowest(min) values of a certain series of an excel chart. I also want to get their positions on the chart itself (from the top or left). I know which way to look, but if you give the code to check which way to measure that will be a bonus. See the drawing below.

   -------------------Left------------------------------------- >
  |        ________________________________________
  |       |                                        |
  |   225 |----------------------------------------|max (highest)
  |   200 |                                        |
  |   175 |                                        |
  |   150 |            -----                       |
|Top| 125 |           /     \                      |
  |   100 |          /       \                     |
  |    75 |   -------         \                    |
  |    50 |  /                 --------------\     |
  |    25 | /                                 \    |
 \|/    0 |----------------------------------------|min (lowest)
  V       |________________________________________|

[This answer](http://stackoverflow.com/a/8773137/445425) while not exactly what you are after may be usefull. BTW thee whatcha-ma-call-it is _IntelliSense_ — chris neilsen, Oct 17 '12 at 03:49

score 2 · Answer 1 · answered Nov 16 '12 at 05:37

You don't need any VBA for this at all.. I know what you want and I have allready created it. I will explain how you do it for the max position as you do similar for the minimum. First we need to find the max Y-axis label and then it gets easy.

Let's break it down to five steps:

A in the formulas: Determine the scale.
B in the formulas: Compare on that scale.
C in the formulas: Measure the interval.
D in the formulas: Round it down.
E in the formulas: Calculate maximum axis-label.

OK, here are the formulas for each step:

=ROUNDDOWN(LOG(MAX(data));1) where data stands for the cells containing your values.
=MAX(data)/10^A)*1.05
=0.2+(B>2)*0.3+(B>5)*0.5
=ROUNDDOWN(B;C)
=(C+D)*10^A

I use Dutch formulas myself, so I might have made a slight translation error. Other than that I promise you this works. The reason for it is, that I have figured how Excel 'thinks' with charts. I will try to explain how this works.

Basically there are three basic ranges Excel uses: from 1 to 2, from 2 to 5 and from 5 to 10.. When a number is higher than that 10, the 10 will be considered as 1 again and you fall back in the first range. That is why we first determine the scale with the LOG formula. If you're new to that, look it up in wiki.

So when we have the scale, we determine which range it falls in. For each of these three ranges the y-axis label intervals are different. So we calculate them in the third step and use it in the fourth step to round number B down. The fifth step simply multiplies it back to its original scale.

And there you go: the max Y-axis label is found. Look at the chart and it should be the same. If you get the idea, you'll find the minimum Y-axis label too. The cool thing now that you can calculate very easily where the label should go EXACTLY cause you know the size of the grid now.

Good luck with that and if you still have questions, let me know.

Patrick

score 0 · Answer 2 · answered Oct 17 '12 at 08:18

It's not clear what you want to achieve, but here's a demo of accessing the Series, Points and Labels of a chart.

Sub MoveLabels()
    Dim sh As Worksheet
    Dim oCh As Chart
    Dim oSers As SeriesCollection
    Dim oSer As Series
    Dim oPts As Points
    Dim oPt As Point
    Dim oLbls As DataLabels
    Dim oLbl As DataLabel
    Dim i As Long, pt As Long


    Set sh = ActiveSheet
    Set oCh = sh.ChartObjects("Chart 8").Chart

    ' Series Collection of a chart
    Set oSers = ch.SeriesCollection
    For Each oSer In oSers
        'Labels collection of a series
        Set oLbls = oSer.DataLabels
        For Each oLbl In oLbls
            ' Label Object

        Next

        'Points collection of a series
        Set oPts = oSers.Points
        For Each oPt In oPts
            ' Label Object
            Set oLbl = oPt.DataLabel
        Next
    Next
End Sub

score 0 · Accepted Answer · answered Oct 18 '12 at 15:53

Well, after some more experimenting (which is one thing VBA is made for) I got it. My specific case has two bar series that are on top of each other. The code is rather long, but it is lightening fast. I think I learned a bit about how to speed up vba in the process!

Sub AdjustReportChart()
    'main report chart
    Dim mrc As Chart
    Dim sercol As SeriesCollection
    Dim axe As Axis, ser As series
    Dim poi1 As Point, poi2 As Point
    Dim i As Integer, j As Integer
    'select the chart
    If hwb Is Nothing Then Call SetGlobals
    Set mrc = mws.ChartObjects("Chart 1").Chart
    'delave all the needed vars
    Dim poi1pos As Double, poi1val As Double
    Dim min1 As Integer, max1 As Integer
    Dim poi2pos As Double, poi2val As Double
    Dim min2 As Integer, max2 As Integer
    'get the chart width params
    Dim width As Integer
    width = Int(mrc.PlotArea.InsideWidth)
    Set sercol = mrc.SeriesCollection
    Set axe = mrc.Axes(2, sercol(1).AxisGroup)
    min1 = axe.MinimumScale
    max1 = axe.MaximumScale
    Set axe = mrc.Axes(2, sercol(2).AxisGroup)
    min2 = axe.MinimumScale
    max2 = axe.MaximumScale
    'start adjusting. 
    For j = 1 To sercol(1).points.Count
        Set poi1 = sercol(1).points(j)
        Set poi2 = sercol(2).points(j)
        poi1pos = poi1.DataLabel.Left
        poi2pos = poi2.DataLabel.Left
        poi1val = poi1.DataLabel.Text
        poi2val = poi2.DataLabel.Text
        poi1pos = (poi1val / (max1 - min1) * width - 6) + 142
        poi2pos = (poi2val / (max2 - min2) * width - 6) + 142
        If poi2pos < poi1pos + (Len(Str(poi1val)) * 6) And _
            poi1pos < poi2pos + (Len(Str(poi2val)) * 6) Then
            poi2pos = poi1pos + (Len(Str(poi1val)) * 6)
        End If
        poi1.DataLabel.Left = poi1pos
        poi2.DataLabel.Left = poi2pos
    Next j
End Sub

How to retrieve the max/min values and locations in Excel Chart object

3 Answers3

Linked