ChartMaxY + ChartMinY

Calculates the maximum of the chart Y Axis to draw, the shifted one, not exact number, shift is calculated based on the differences between Maxi and Mini
Needed to have multiple charts to all show same max and min y axis range to be consistent among all.

CodeFunctionName
What is this?

Public

Tested

Original Work
Function ChartMaxY(Maxi, Mini)
' Calculates the maximum of the chart Y Axis to draw
' To add some space above or below the Max or Min
Diff = Round(Abs(Maxi - Mini), 1) / 50
Roo = Abs(Maxi - Mini) / 10
If Roo > 1 Then
Roo = Int(Roo) ' Round to integer only if it is greator than 1
Else
Roo = Round(Roo, 2) ' for less than 1, do not round to int
End If
If Roo = 0 Then Roo = 1
If Val(Application.Version) >= 14 And Val(SettingRead("Ceiling_Floor_Flag")) = 2 Then ' Excel2010 or later
ChartMaxY = WorksheetFunction.Ceiling_Precise((Maxi + (Diff / 50)), Roo)
Else
If Sgn(Roo) = Sgn(Maxi) Then
ChartMaxY = WorksheetFunction.Ceiling((Maxi + (Diff / 50)), Roo)
Else
ChartMaxY = Sgn(Maxi) * WorksheetFunction.Floor((Abs(Maxi) + (Diff / 50)), Abs(Roo))
End If
End If
End Function
Function ChartMinY(Maxi, Mini)
' Calculates the minimum of the chart Y Axis to draw
' To add some space above or below the Max or Min
Diff = Round(Abs(Maxi - Mini), 1)
Roo = Abs(Maxi - Mini) / 10
If Roo > 1 Then
Roo = Int(Roo) ' Round to integer only if it is greator than 1
Else
Roo = Round(Roo, 2) ' for less than 1, do not round to int
End If
If Roo = 0 Then Roo = 1
If Val(Application.Version) >= 14 And Val(SettingRead("Ceiling_Floor_Flag")) = 2 Then ' Excel2010 or later
ChartMinY = WorksheetFunction.Floor_Precise((Mini - (Diff / 25)), Roo)
Else
If Sgn(Roo) = Sgn(Mini) Then
ChartMinY = WorksheetFunction.Floor((Mini - (Diff / 25)), Roo)
Else
ChartMinY = Sgn(Mini) * WorksheetFunction.Ceiling((Abs(Mini) - (Diff / 25)), Abs(Roo))
End If
End If
End Function

Maxi, Mini in both

Views 3,765

Downloads 1,375

CodeID
DB ID