How to get max / min and location values ​​in an Excel Chart object

Question

How to get the maximum and minimum values ​​of the excel series collection, as well as their position on the diagram? I want to calculate where the label should be for each point in the series and move it accordingly.

I do it so that they look beautiful, in your opinion. The numbers, as a rule, ruined everything if I just blindly move the labels. I am sure that I could have a number in the base of the diagram, but that is not what I am trying to do. It doesn’t help the Chart object much. It’s difficult to work with the Object thing-a-ma-jig object (this is whatcha-ma-call-it, which appears when you click the period after the name of the object).

Edit: What I'm trying to achieve is searching for the highest (maximum) and minimum (min) values ​​of a particular excel chart series. I also want to get my positions on the chart itself (top or left). I know which way to look, but if you give a code to check which measurement method will be a bonus. See the figure below.

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

Answer 1

Well, after some experimentation (what VBA was created for), I got it. My particular case has two series of bars that are on top of each other. The code is quite long, but it burns quickly. I think I learned a little about how to speed up vba in this 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 

Answer 2

You don't need VBA at all. I know what you want, and I already created it. I will explain how you do it for the maximum position, since you do this for the minimum. First we need to find the maximum mark of the Y axis, and then it becomes easy.

Let me break it down into five steps:

• A in the formulas: Determine the scale.
• B in the formulas: Compare on this scale.
• C in the formulas: Measure the interval.
• D in the formulas: round it.
• E in the formulas: Calculate the maximum mark axis.

OK, here are the formulas for each step:

• = ROUNDDOWN (LOG (MAX (data)); 1) where data indicates 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 myself use Dutch formulas, so I could make a small translation mistake. Other than that, I promise it will work. The reason is that I understood how Excel “thinks” with graphs. I will try to explain how this works.

Basically, there are three main ranges for using Excel: from 1 to 2, from 2 to 5, and from 5 to 10. When the number is greater than 10, 10 will be considered 1 again, and you will return to the first range. This is why we first scale using the LOG formula. If you are new to this, check it out on the wiki.

So, when we have a scale, we determine in which range it falls. For each of these three ranges, the intervals between the Y axis labels are different. Therefore, we calculate them in the third step and use it in the fourth step to round the number B down. The fifth step simply multiplies it by the original scale.

And here you are: the Max Y-axis label is found. Look at the chart and it should be the same. If you get this idea, you will also find the minimum Y-axis label. Now the cool thing is that you can easily figure out where the label should go EXACTLY because now you know the size of the grid.

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

Patrick

Answer 3

It is not clear what you want to achieve, but there is a demonstration of access to Series , Points and Labels diagrams.

 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