How can i get the MAX value for the Y and X axes ?

  • Thread starter Thread starter Radixa
  • Start date Start date
R

Radixa

Hi all,

I want to get the max value for the Y and X axes on a plotArea, not the max
values for series on this chart.

Is it possible with VBA ?

TIA
Rad
 
This page shows how to apply cell values to the axis scale.

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

If you want to know what Excel is currently using for the scale
parameters, try something like this:

Sub ShowAxisMax()
Dim x As Double, y As Double, s As String
With ActiveChart
On Error Resume Next
' Error if not a value scale axis
x = .Axes(1).MaximumScale
If Err.Number = 0 Then
s = "X max = " & x & vbCrLf
End If
y = .Axes(2).MaximumScale
s = s & "Y max = " & y
MsgBox s
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Radixa,

I don't quite understand your request. If your looking for the plot height
and width, this macro will extract it:

Sub PlotSize()
ActiveChart.PlotArea.Select
MsgBox "Plot Height: " & Selection.Height & _
" Plot Width: " & Selection.Width
End Sub

If you're looking for the maximum and minimum for the X and Y axis, the X
and Y axis labels should tell you that.

Maybe you can post back if this doesn't work.
 
Use the Axis object's maximumscale (or minimumscale) property

For example, in the immediate window:
?activechart.Axes(xlvalue).maximumscale

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Ok, thanks Tushar.

Here is the result to write labels on the graph :
(I get the label from a comment on the cell)

================================
iPlotAreaInsideWidth = ActiveChart.PlotArea.InsideWidth
iPlotAreaInsideHeight = ActiveChart.PlotArea.InsideHeight
vYMax = ActiveChart.Axes(xlValue, CInt(sYAxe)).MaximumScale
iIndexMax = UBound(ActiveChart.SeriesCollection(iSeriesIndex).Values)

iIndex = 1
For Each oCell In oRange
If Not (oCell.Comment Is Nothing) Then
vYTemp = oCell.Value
iXlabel = iIndex * iPlotAreaInsideWidth / iIndexMax
iYlabel = ((vYMax - vYTemp) * iPlotAreaInsideHeight /
vYMax) - 10

DrawComment oCell.Comment.Text, iXlabel, iYlabel, 10
End If
iIndex = iIndex + 1
Next oCell

....

Function DrawComment(ByVal strTexte, intX, intY, intFontSize)
Dim myChar As Object

Set myChar = ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal,
intX, intY, 0#, 0#) _
.TextFrame.Characters
With myChar
.Text = strTexte
.Font.Name = "Arial"
.Font.FontStyle = "Normal"
.Font.Size = intFontSize
.Font.ColorIndex = xlAutomatic
End With
End Function
================================

It works fine.

Regards
Rad.
 
Back
Top