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

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/...nkToSheet.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 wrote:

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

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.

----
Regards,
John Mansfield
http://www.pdbook.com


"Radixa" wrote:

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

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

In article ,
says...
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

Thanks a lot !

It's exactly what i need. Now, i can add label to some points of my chart
using vba macro without add-in like XY Chart Labeler
(http://www.appspro.com/Utilities/Utilities.htm)

(Thanks also to John Mansfield & Tushar Mehta for their help)

Rad


"Jon Peltier" a écrit dans le message
de ...
This page shows how to apply cell values to the axis scale.

http://peltiertech.com/Excel/Charts/...nkToSheet.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 wrote:

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

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(msoTextOrientationHori zontal,
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.


"Tushar Mehta" a écrit dans le
message de om...
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

In article ,
says...
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