To automate the values for major and minor unit for a chart using VBA

Hi all,

I would like to automate the process of assigning major and mior unit. Now am finding out the maximum and mininum values and assinging them to the chart. But the problem is the chart is becoming bigger some times.


To avoid this I think, minor and major units should also be set. Please let me know how determine the units.


Thanks & Regards,
Abhiram:friends:

Hi Abhiram,

I assume you're talking about a MSGraph chart...
Assuming "objChart" is your chart object, you can get to the axis properties like this[VBA]With objChart.Axes(xlValue, xlPrimary)
.MajorUnit = 25
.MinorUnit = 5
End With[/VBA]

Quote:

---

Originally Posted by Killian

Hi Abhiram,

I assume you're talking about a MSGraph chart...
Assuming "objChart" is your chart object, you can get to the axis properties like this[vba]With objChart.Axes(xlValue, xlPrimary)
.MajorUnit = 25
.MinorUnit = 5
End With[/vba]

---

Thats correct. The minimum and maximum values always change for the chart. So the major and minor uint also should change accordingly.

So is there any method to determeine.


Regards,
Abhiram

Ahh... I understand now, you want the min and max values from the data, then set the Axis range...
Is there any reason why you can't set MinimumScaleIsAuto/MaximumScaleIsAuto to True?
Other wise, where do you get the data for the chart? Can you capture the min and max at that stage?

Quote:

---

Originally Posted by Killian

Ahh... I understand now, you want the min and max values from the data, then set the Axis range...
Is there any reason why you can't set MinimumScaleIsAuto/MaximumScaleIsAuto to True?
Other wise, where do you get the data for the chart? Can you capture the min and max at that stage?

---

Hi Killian,

The am trying to get the data from the chart data sheet. I will try to set using MinimumScaleIsAuto/MaximumScaleIsAuto.

Can you please explain me how Maximum/Minimum SclaseIsAuto calcutlates the value

Thanks & Regards,
Abhiram.

Well I don't know the algorithm used by MS... one way to make it happen would be to use the Auto major unit and just add one either side.
I think the only way to get the data point values is by going through the whole datasheet - or at least the used range.

I've had a go at doing this - seems to work ok but I haven't tested different chart types and data[VBA]Dim objChart As Chart
Dim ds As DataSheet
Dim max_val As Single
Dim min_val As Single
Dim rng As Range
Dim rngAll As Range
Dim r As Long, c As Long

'this code runs if an MSGraph object is selected
If ActiveWindow.Selection.Type = ppSelectionShapes Then
If Left(ActiveWindow.Selection.ShapeRange.OLEFormat.ProgID, 13) = "MSGraph.Chart" Then
Set objChart = ActiveWindow.Selection.ShapeRange.OLEFormat.Object
Set ds = objChart.Application.DataSheet
'get the used range (requires contiguous data)
Do
r = r + 1
Loop Until ds.Rows(r).Include = False
Do
c = c + 1
Loop Until ds.Columns(c).Include = False
Set rngAll = ds.Range(ds.Cells(2, 2), ds.Cells(r, c))
'loop through the used range to get max and min values
For Each rng In rngAll
If rng.Value > max_val Then max_val = rng.Value
If rng.Value < min_val Then min_val = rng.Value
Next
'calc the min & max scale based on the automatic major unit
'and set the axis to the min & max values
With objChart.Axes(xlValue, xlPrimary)
.MajorUnitIsAuto = True
.MaximumScale = .MajorUnit
Do
.MaximumScale = .MaximumScale + .MajorUnit
Loop Until .MaximumScale >= max_val
.MinimumScale = .MaximumScale
Do
.MinimumScale = .MinimumScale - .MajorUnit
Loop Until .MinimumScale <= min_val
End With
End If
End If[/VBA]

Hi Killian,
Its working now.

Thanks for the needful.

Regards,
Abhiram.