Consulting

Results 1 to 7 of 7

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

  1. #1

    Thumbs up 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

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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]
    K :-)

  3. #3

    Hi Killian,

    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

  4. #4
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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?
    K :-)

  5. #5
    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.

  6. #6
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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]
    K :-)

  7. #7
    Hi Killian,
    Its working now.

    Thanks for the needful.

    Regards,
    Abhiram.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •